>>You can put your condition into JOIN condition or INTO where, the result will be the same.
>>
>>select M.*, C.* from Master M inner join Child C on M.PK = C.FK and C.SomeField > 0
>>
>>or
>>
>>select M.*, C.* from Master M inner join Child C on M.PK = C.FK
>>where C.SomeField > 0
>>
>>If you're talking about LEFT JOIN, then the situation is different and you only can place condition into JOIN condition to get correct results.
>
>I tried that several times and was not able to avoid those records to be part of the result. I tried both approaches as mentioned here. I also tried WHERE primarykey NOT IN () and vice versa.
I think I misread your situation. You're talking about relational division here.
It's a slightly more complex problem, but Peter Larsson posted a great solution to it here
http://sqlblog.com/blogs/peter_larsson/archive/2010/06/30/relational-algebra.aspx
If it's not broken, fix it until it is.
My Blog