CODEWITHSUNDEEP
sqlsql-server
nightmare637
nightmare637

Reputation: 635

WHERE clause not hitting on LEFT JOIN

I am trying to select ID, a, b from DatabaseAlpha whose IS_CURRENT = 'y', along with any data from DatabaseBravo who has the same ID as DatabaseAlpha and whose field IS_CURRENT = 'Y'. I am using a LEFT JOIN because all IDs in DatabaseBravo will have a corresponding one in DatabaseAlpha. But not all IDs in DatabaseAlpha will have on in DatabaseBravo (hence, I expect NULLS to return in the query on the right side). There will only be one instance of IS_CURRENT = 'Y' per ID (but multiple IS_CURRENT = 'N').

I have a query as follows:

SELECT ID, a, b 
FROM DatabaseAlpha x
LEFT JOIN DatabaseBravo y ON x.ID = y.ID
                          AND x.IS_CURRENT = 'Y' 
                          AND y.IS_CURRENT = 'Y'

However, the result is not what I want. This query is showing multiple entries with the SAME ID from my DatabaseAlpha table and NULL from DatabaseBravo, having both IS_CURRENT = 'Y' and IS_CURRENT = 'N'.

For example, take ID = 357. There are 6 entries in DatabaseAlpha with this ID, and zero in DatabaseBravo. Of the 6 entries, 5 of them have IS_CURRENT = 'N' and only one has IS_CURRENT = 'Y'. The result I want is for only the entry with IS_CURRENT = 'Y' to return for this ID. However, the actual result from the query is that all 6 entries are returned, including the ones that say IS_CURRENT = 'N'.

I've tried changing my query in three ways, but they all failed:

WHERE x.IS_CURRENT = 'Y' AND y.IS_CURRENT = 'Y'
WHERE x.IS_CURRENT = 'Y' OR y.IS_CURRENT = 'Y'
AND x.IS_CURRENT = 'Y' OR y.IS_CURRENT = 'Y'

What am I doing wrong or not seeing here?

Upvotes: 1

Views: 49

Answers (1)

sgeddes
sgeddes

Reputation: 62861

You need to move part of the join condition to where criteria instead:

SELECT ID, a, b 
FROM DatabaseAlpha x
    LEFT JOIN DatabaseBravo y on x.ID = y.ID and y.IS_CURRENT = 'Y'
WHERE x.IS_CURRENT = 'Y'

This will return all records from the DatabaseAlpha table that match on is_current = 'Y' and because you are using an outer join, only those matching from the join condition will be returned from DatabaseBravo, in this case is_current = 'Y' there as well.

Upvotes: 4

Related Questions