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.

+2


source to share


5 answers


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.

0


source


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

      

+2


source


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.

0


source


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.

0


source


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

      

0


source







All Articles