CODEWITHSUNDEEP
sqlsql-server
Challenge
Challenge

Reputation: 1

Re-Writing a SQL Statement with a Subquery to Have a Join

I have to re-write a SQL statement with a subquery so that it has a join for my job. So far, this is what I have.

SELECT * FROM Table_A
WHERE TABLE_A.A_ID NOT IN
(SELECT LK.A_ID FROM Link_Table LK
LEFT JOIN Table_B B
ON B.B_ID = LK.B_ID)

I am really having a hard time with this. I feel like this is because of the link tables though. Can anyone give me advice on altering this query?

Upvotes: 0

Views: 33

Answers (1)

Thom A
Thom A

Reputation: 95620

Seems like you want a LEFT JOin with a IS NULL in the where:

SELECT {Column list} --Don't use *
FROM dbo.Table_A A
     LEFT JOIN dbo.Link_Table LK ON A.A_ID = LK.A_ID 
WHERE LK.A_ID IS NULL;

You don't need the reference to Table_B at all here.

Personally, however, I would prefer an EXISTS, but that is a subquery again:

SELECT {Column List}
FROM dbo.Table_A A
WHERE NOT EXISTS (SELECT 1
                  FROM dbo.Link_Table LK
                  WHERE A.A_ID = LK.A_ID);

Upvotes: 1

Related Questions