SQL LEFT outer join with only some rows from the right

left-joinsql

I have two tables TABLE_A and TABLE_B having the joined column as the employee number EMPNO.

I want to do a normal left outer join. However, TABLE_B has certain records that are soft-deleted (status='D'), I want these to be included. Just to clarify, TABLE_B could have active records (status= null/a/anything) as well as deleted records, in this case i don't want that employee in my result. If however there are only deleted records of the employee in TABLE_B i want the employee to be included in the result.I hope i'm making my requirement clear. (I could do a lengthy qrslt kind of thingy and get what I want, but I figure there has to be a more optimized way of doing this using the join syntax). Would appreciate any suggestions(even without the join). His newbness is trying the following query without the desired result:

SELECT TABLE_A.EMPNO
FROM   TABLE_A
LEFT OUTER JOIN TABLE_B ON TABLE_A.EMPNO = TABLE_B.EMPNO AND TABLE_B.STATUS<>'D'

Much appreciate any help.

Best Answer

Just to clarify -- all records from TABLE_A should appear, unless there are rows in table B with statues other than 'D'?

You'll need at least one non-null column on B (I'll use 'B.ID' as an example, and this approach should work):

SELECT TABLE_A.EMPNO
FROM TABLE_A
LEFT OUTER JOIN TABLE_B ON
  (TABLE_A.EMPNO = TABLE_B.EMPNO)
  AND (TABLE_B.STATUS <> 'D' OR TABLE_B.STATUS IS NULL)
WHERE
  TABLE_B.ID IS NULL

That is, reverse the logic you might think -- join onto TABLE_B only where you have rows that would exclude TABLE_A entries, and then use the IS NULL at the end to exclude those. This means that only those which didn't match (those with no row in TABLE_B, or with only 'D' rows) get included.

An alternative might be

SELECT TABLE_A.EMPNO
FROM TABLE_A
WHERE NOT EXISTS (
  SELECT * FROM TABLE_B 
  WHERE TABLE_B.EMPNO = TABLE_A.EMPNO
  AND (TABLE_B.STATUS <> 'D' OR TABLE_B.STATUS IS NULL)
)
Related Topic