CODEWITHSUNDEEP
mysql
Madhan
Madhan

Reputation: 107

How to use IN condition along with LEFT OUTER JOIN and ON

I want to use IN condition, it works with the value not work with the variable of another table, Here is my sample

SELECT  A.*,B.* 
FROM table1 AS A 
LEFT JOIN erp_wh_salhd AS B ON B.InvNo = '15'  AND A.InvNo IN (B.filter1) 
//Not working  Comming only one row for B table

if I pass values directly means it works, i.e 

SELECT  A.*,B.* 
FROM table1 AS A 
LEFT JOIN erp_wh_salhd AS B ON B.InvNo = '15'  AND A.InvNo IN (2,3)  
// Working. table B have two values

Upvotes: 0

Views: 53

Answers (3)

Thorsten Kettner
Thorsten Kettner

Reputation: 94904

The IN clause allows for multiple values. In your first query you only provide one value, however:

AND A.InvNo IN (B.filter1)

That is equal to

AND A.InvNo = B.filter1

So you only join those B rows to an A row where the B row's InvNo is 15 and its filter1 equals A's InvNo.

In your second query you join all B rows whose InvNo is 15 to all A rows whose InvNo is either 2 or 3.

Two completely different queries, you cannot really compare.

Upvotes: 0

ScaisEdge
ScaisEdge

Reputation: 133360

Do the fact you are using join you could try using =

    SELECT  A.*,B.* 
    FROM table1 AS A 
    LEFT JOIN erp_wh_salhd AS B ON B.InvNo = '15'  
        AND A.InvNo  = B.filter1

Upvotes: 1

Nick
Nick

Reputation: 147166

If the filter1 field in erp_wh_salhd is a comma separated list of values, you will need to use FIND_IN_SET instead of IN:

SELECT  A.*,B.* 
FROM table1 AS A 
LEFT JOIN erp_wh_salhd AS B ON B.InvNo = '15'  AND FIND_IN_SET(A.InvNo, B.filter1)

Upvotes: 1

Related Questions