Reputation: 245
I'm trying to figure out how to get the same results using joins, as I would using a not exists condition.
For example, if I had the following two tables:
TABLE 1
--------------
|ID | EXT_ID |
|1 | A |
|2 | B |
|3 | C |
--------------
TABLE 2
-------------------------
|EXT_ID | TB1_ID |PRIMARY|
|A | 1 |1 |
|A | 1 |0 |
|B | 2 |0 |
|B | 2 |0 |
-------------------------
If I'm looking to find the records from table 1 that do not have a primary flag of 1 in table 2, for records that actually have a child in table 2 (to exclude orphans), I could simply write the following (expected to return only ID 2 from table 1):
SELECT TB1.ID FROM Table1 TB1
JOIN Table2
ON Table1.EXT_ID = Table2.EXT_ID
WHERE Table2.Primary = 0
AND NOT EXISTS
(
SELECT * FROM Table2 TB2
WHERE TB1.ID = TB2.TB1_ID
AND TB2.PRIMARY = 1
)
Is there a way to do this with joins? And if so, would there be much efficiency in using the not exists vs. a join?
Thanks in advance!
EDIT: fixed tables.
Upvotes: 0
Views: 48
Reputation: 49270
with x as (select Ext_ID, Tb1_ID, Sum(Primary) as sum_primary
from Table2 group by Ext_ID,Tb1_ID)
SELECT TB1.ID
FROM Table1 TB1 JOIN x
ON Table1.EXT_ID = x.EXT_ID
where x.sum_primary = 0
You can use a CTE for this.
Upvotes: 1
Reputation: 13949
SELECT
TB1.ID, TB1.EXT_ID
FROM
TABLE1 TB1
JOIN TABLE2 TB2 ON TB1.ID = TB2.TB1_ID AND TB1.EXT_ID = Table2.EXT_ID
GROUP BY
TB1.ID, TB1.EXT_ID
HAVING MAX(TB2.[PRIMARY]) = 0
Upvotes: 0