Left join with multiple conditions

[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.

[ujjwalguptaofficial]

have you tried where inside join ?

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

[shaangidwani]

Yes, we have tried but it will display only one record looks like it will remove the left join and work as where condition only.

If you check SQL query it will show all the left table data including where condition data. ex : It will show all the customers who are not exits on orders and include 90. But your suggested query will only give one record which are in where condition.

Kindly suggest on this asap. Will appreciate your help.

[ujjwalguptaofficial]

aha i see - you are right. Let me see

[ujjwalguptaofficial]

The current implementation is confused b/w "&" and "where with join", let me fix it right away.

[ujjwalguptaofficial]

I have fixed in version - 4.5.7, please try and let me know. The where query inside join works for and with join.

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

This should work now.

[ujjwalguptaofficial]

Here is idbstudio link - https://ujjwalguptaofficial.github.io/idbstudio/?db=Demo&query=select(%7B%0A%20%20%20%20from%3A%20'Customers'%2C%0A%20%20%20%20join%3A%20%7B%0A%20%20%20%20%20%20%20%20type%3A%20'left'%2C%0A%20%20%20%20%20%20%20%20with%3A%20'Orders'%2C%0A%20%20%20%20%20%20%20%20on%3A%20%22Customers.customerId%20%3D%20Orders.customerId%22%2C%0A%20%20%20%20%20%20%20%20where%3A%20%7B%0A%20%20%20%20%20%20%20%20%20%20%20%20customerId%3A%2090%0A%20%20%20%20%20%20%20%20%7D%0A%20%20%20%20%7D%0A%7D)%3B

[shaangidwani]

Hey, @ujjwalguptaofficial Thanks we have an update version and it is working fine now :-)

You saved our life.