CODEWITHSUNDEEP
mysqljoin
Manish Tiwari
Manish Tiwari

Reputation: 1866

How to join two tables using left join with multiple conditions?

I want to join two tables using left join on multiple conditions.

I have two STS_CD flags AC or IA.

I want to get data where STS_CD='AC' but i got it on both AC or IA.

My Query is:

SELECT `bldr`.`id`     AS `bldr_ID`, 
       `bldr`.`sts_cd` AS `bldr_STS_CD`, 
       bldr.shrt_nm 
FROM   `bldr` 
       LEFT JOIN `bldr_img` 
              ON `bldr`.`id` = `bldr_img`.`prnt_id` 
                 AND `bldr_img`.`img_cat_cd` = 'LG' 
                 AND `bldr`.`sts_cd` = 'AC' 
                 AND `bldr_img`.`sts_cd` = 'AC' 
ORDER  BY `bldr`.`id`

How do I do this?

enter image description here

Upvotes: 1

Views: 123

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269693

This is your query:

SELECT bldr.ID as bldr_ID, bldr.STS_CD AS bldr_STS_CD,bldr.SHRT_NM
FROM bldr LEFT JOIN
     bldr_img
    ON bldr.ID = bldr_img.PRNT_ID AND
       bldr_img.IMG_CAT_CD = 'LG' AND
       bldr.STS_CD = 'AC' AND
       bldr_img.STS_CD = 'AC'
ORDER BY bldr.ID;

When using LEFT JOIN, conditions on the second table should go in the ON clause. Conditions on the first table should be in the WHERE (except for the JOIN condition, of course). So try this:

SELECT bldr.ID as bldr_ID, bldr.STS_CD AS bldr_STS_CD,bldr.SHRT_NM
FROM bldr LEFT JOIN
     bldr_img
    ON bldr.ID = bldr_img.PRNT_ID AND
       bldr_img.IMG_CAT_CD = 'LG' AND
       bldr_img.STS_CD = 'AC'
WHERE bldr.STS_CD = 'AC'
ORDER BY bldr.ID;

Why? Although this seems arcane it makes perfect sense. A LEFT JOIN keeps all rows in the first tables regardless of whether the ON clause evaluates to true, false, or even NULL. It doesn't "know" whether the condition is on the first table or the second. Hence, filters on the first table are not effective.

Upvotes: 2

Related Questions