SQL LEFT outer join with multiple rows on the right?
I have two tables TABLE_A
and both TABLE_B
having a merged column as employee number EMPNO
.
I want to make a normal left outer join. However, TABLE_B
has certain entries that are softly deleted ( status='D'
), I want them to be included. To clarify, TABLE_B
can have active records (status = null / a / anything) as well as deleted records, in which case I don't want this employee in my result. If, however, there are only deleted employee records in TABLE_B
, I want the employee to be included in the result. Hopefully I'll make my requirement clear. (I could do a long qrslt kind of thing and get what I want, but I believe there should be a more streamlined way to do this using join syntax). Would be grateful for any suggestions (even without participation). Its newbness tries to execute 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'
I really appreciate any help.
source to share
ah crud, this seems to work>
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 have more information to listen to, please do not hesitate.
UPDATE: Saw this question after a while and thought I'd add some more useful information: this link has good information on ANSI syntax - http://www.oracle-base.com/articles/9i/ANSIISOSQLSupport.php
In particular, this part from the linked page is informative:
Additional filter conditions can be added to the join to use AND to form a complex join. This is often necessary when filter conditions are needed to restrict an external connection. If these filter conditions are placed in a WHERE clause and the outer join returns NULL for the filter column, the row will be selected. if the filter condition is encoded as part of a join, the situation can be avoided.
source to share
Just for clarification - should all records from TABLE_A appear if there are no rows in table B with statuses other than 'D'?
In B, you need at least one non-null column (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, change the logic you might think - join TABLE_B only where you have rows that will exclude TABLE_A records, and then use IS NULL at the end to exclude them. This means that only those that do not match are included (those that do not have a row in TABLE_B or only with "D" lines).
An alternative could 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)
)
source to share
In the next request, you will get employee records that are not deleted, or only deleted records are deleted.
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)
It's in ANSI SQL, but if I knew your RDBMS I could give a more specific solution that might be a little more elegant.
source to share
SELECT A.*, B.*
FROM
Table_A A
INNER JOIN Table_B B
ON A.EmpNo = B.EmpNo
WHERE
NOT EXISTS (
SELECT *
FROM Table_B X
WHERE
A.EmpNo = X.EmpNo
AND X.Status <> 'D'
)
I think this does the trick. A left join is not required because you only want to include employees with all (and at least one) rows deleted.
source to share
This is how I understand the question. You only need to include employees that meet one of the following conditions:
-
the employee has only (soft) deleted lines in
TABLE_B
; -
the employee only has lines not deleted in
TABLE_B
; -
the employee has no lines at
TABLE_B
all.
In other words, if an employee has both deleted and non-deleted lines in TABLE_B
, omit that employee, otherwise include them.
This is how I think it can be solved:
SELECT DISTINCT a.EMPNO
FROM TABLE_A a
LEFT JOIN TABLE_B b1 ON a.EMPNO = b1.EMPNO
LEFT JOIN TABLE_B b2 ON b1.EMPNO = b2.EMPNO
AND (b1.STATUS = 'D' AND (b2.STATUS <> 'D' OR b2 IS NULL) OR
b2.STATUS = 'D' AND (b1.STATUS <> 'D' OR b1 IS NULL))
WHERE b2.EMPNO /* or whatever non-nullable column there is */ IS NULL
Alternatively, you can use grouping:
SELECT a.EMPNO
FROM TABLE_A a
LEFT JOIN TABLE_B b ON a.EMPNO = b1.EMPNO
GROUP BY a.EMPNO
HAVING 0 IN (COUNT(CASE b.STATUS WHEN 'D' THEN 1 ELSE NULL END),
COUNT(CASE b.STATUS WHEN 'D' THEN NULL ELSE 1 END))
source to share