CODEWITHSUNDEEP
sqlt-sql
Chris
Chris

Reputation: 7611

SQL excluding via Join

This may be pretty simple but I'm finding it difficult to wrap my head around this.

Basically, I have 2 tables, a and b. 'b' contains a list of all possible items, and 'a' contains a row which links to an item in 'b', and also a parent number. i.e, to display the rows in a with their information I do something like this:

select a.field1, a.field2, b.description
from a inner join b on a.itemid = b.itemid
where a.parentnumber = @parentnumber

That sort of thing work sfine. But I also want a dropdown box to display all that items that are not listed for that parent account in a. How would I do this?

Upvotes: 2

Views: 465

Answers (2)

user3017734
user3017734

Reputation: 7

By using a left join to this subquery, you can give an alias and use this alias to perform a null-check. I prefer this approach because the alias, which contains the results of the subquery, can be used through the whole query.

SELECT *
FROM b

LEFT JOIN
(
SELECT itemid
FROM   a
Where  a.parentnumber = @parentnumber
) As Sub On b.itemid = sub.itemid

WHERE sub.itemid IS NULL

Upvotes: 1

Quassnoi
Quassnoi

Reputation: 425341

SELECT  *
FROM    b
WHERE   itemid NOT IN
        (
        SELECT  itemid
        FROM    a
        WHERE   a.parentnumber = @parentnumber
        )

Upvotes: 9

Related Questions