I am new to T-SQL and not as experienced with SQL as I would like to be. I am hoping someone can help me write a routine to do something. There is actually more to my problem, but I am hoping that if someone can help here, I can figure out the rest. At least lead me in the right direction with the logic of how this is done...

I have 2 tables, linked in a standard 1 to many relationship.

MasterTable
- IDNum
- Name

LinkedTable
- IDNum (linked field)
- Info1

What I need is every MasterTable.IDNum where no records of LinkedTable.Info1=100 exist

MasterTable (Sample Data)
(IDNum, Name)
1, PersonA
2, PersonB
3, PersonB
4, PersonB
5 , PersonB

LinkedTable (Sample Data)
(IDNum, Info1)
1, 5
1, 6
2,100
3,5
3,100
4, 8
5, 10
5, 15
5, 160

So, the result of the query I want would be:

1
4
5

Please help? I'm at the point where the only thing I can think of is to perform a preliminary query, then step through the complete resultset and physically compare values. Alternatively, create a table, run another query on it. Both of these options seem like they would too slow on a large table.

DB,


Select a.IDNum
From MasterTable as a left join LinkedTable As b on
a.IDNum = b.IDNum
Where b.IDNum Is Null


Wayne

SELECT IDNum
FROM MasterTable
EXCEPT
SELECT IDNum
FROM LinkedTable
WHERE Info1=100;

Thanks dportas. I was not aware of the "EXCEPT" keyword. I'm getting there now.