0

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!

4
Contributors
4
Replies
5
Views
7 Years
Discussion Span
Last Post by kplcjl
0

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
0

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.

0

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

Edited by urtrivedi: n/a

0

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?

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.