CODEWITHSUNDEEP
sqlt-sql
tchock
tchock

Reputation: 245

Equivalent query using joins, instead of not exists

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

Answers (2)

Vamsi Prabhala
Vamsi Prabhala

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

JamieD77
JamieD77

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

Related Questions