SQL LEFT outer join with only some rows from the right?

Question

Hi noble SOuls,

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.

Answer 1

ah crud, this apparently works ><

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

If you guys have any extra info to chime in with though, please feel free.

UPDATE: Saw this question after sometime and thought i'll add more helpful info: This link has good info regarding ANSI syntax - http://www.oracle-base.com/articles/9i/ANSIISOSQLSupport.php

In particular this part from the linked page is informative:

Extra filter conditions can be added to the join to using AND to form a complex join. These are often necessary when filter conditions are required to restrict an outer join. If these filter conditions are placed in the WHERE clause and the outer join returns a NULL value for the filter column the row would be thrown away. if the filter condition is coded as part of the join the situation can be avoided.

Answer 2

The following query will get you the employee records that aren't deleted, or only the employ only has deleted records.

select
    a.*
from
    table_a a
    left join table_b b on
        a.empno = b.empno
where
    b.status <> 'D'
    or (b.status = 'D' and 
        (select count(distinct status) from table_b where empno = a.empno) = 1)

This is in ANSI SQL, but if I knew your RDBMS, I could give a more specific solution that may be a bit more elegant.

Answer 3

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)
)