Left join with multiple condition

[shaangidwani]

Left join with multiple conditions

We need to add multiple condition in left join.

Here is the sql demo script for the same :

========================================
Table creation script

CREATE TABLE A
(
PracticeID INT
,FunctionID INT
,FunctionName VARCHAR(10)
)
GO
CREATE TABLE B
(
PatientID INT
,FunctionID INT
,FunctionValue VARCHAR(10)
)
GO
INSERT INTO A SELECT 123,1,'A'
INSERT INTO A SELECT 123,2,'B'
INSERT INTO A SELECT 123,3,'C'
INSERT INTO A SELECT 123,4,'D'
GO
INSERT INTO B SELECT 11,1,'AAA'
INSERT INTO B SELECT 11,2,'BBB'
INSERT INTO B SELECT 22,3,'CCC'
INSERT INTO B SELECT 22,4,'DDD'
GO

And output of SQL is :

----------Final Output
SELECT * FROM A
LEFT JOIN B ON B.FunctionID =A.FunctionID AND B.PatientID = 11
WHERE PracticeID = 123

You can change the B.PatientID = anything it will display the four records as left join is working fine on sql.

==================================================================

When we try to add multiple conditions on IDB studio we are unable to do this ex :

select({
from: 'Customers',
join: {
type: 'left',
with: 'Orders',
on: "Customers.customerId = Orders.customerId and Customers = 90"
}
});

And if we add in where condition then it will be converted to inner instead of left.

Kindly suggest on this asap.

[shaangidwani]

Can anyone have an idea please help!!

will appreciate your help