SQL excluding via Join

Question

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?

Answer 1

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