SQL LEFT Join using where

[brookstone]

Hi,

This should be a simple query but I am not able to figure out how to get the result I want. I have two tables A and B and want A left Join B however, I do not want all of table A. I want to restrict table A where table A.status="active" and B.Flag='Y'

SELECT * FROM A LEFT JOIN B ON A.ID=B.FK_ID_A - I don't know what to do after this.

Thanks!

[darkagn]

Hi brookstone and welcome to DaniWeb :)

You can simply add your where statement after the on statement. So something like this is ok:

SELECT * 
FROM A 
LEFT JOIN B 
ON A.ID=B.FK_ID_A
WHERE A.STATUS = 'ACTIVE' 
AND B.FLAG = 'Y'
-- you can also add an ORDER BY statement here to sort the returned data

[brookstone]

Thank you for your quick response. Sorry, I phrased my question incorrectly.

I would like all entries from table A but ONLY display B.FLAG status where B.FLAG='Y'

If I use the where statement, it will only return rows from table A and B that have B.FLAG='Y'

I hope I am explaining this well. Thank you.

[urtrivedi]

Following query will give you all records that are in A, whenever b.flag is 'Y' it will show 'Y' other wise it will show null. You may still apply where clause at the end of query to filter your records.

SELECT a.*, case when b.flag='Y' then b.FLAG ELSE NULL END FLAG FROM A LEFT JOIN B ON A.ID=B.FK_ID_A

[kplcjl]

Following query will give you all records that are in A, whenever b.flag is 'Y' it will show 'Y' other wise it will show null. You may still apply where clause at the end of query to filter your records.

SELECT a.*, case when b.flag='Y' then b.FLAG ELSE NULL END FLAG FROM A LEFT JOIN B ON A.ID=B.FK_ID_A

Correct process, but I'm confused. Do you want all A records or only where A.status="active"? If all why did you bring up the active part in the original post?