>I have a situation that I have been trying to resolve but was unable to up until now. I have a one to many relationships in a SQL where I need to use a condition on the INNER JOIN table. So, for example, the INNER JOIN table might have three records related to one record from the master table in the SQL. But, I need to assure to take only master records where all the INNER JOIN records for that table would satisfy a condition. What could be an example of such a query?
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.
If it's not broken, fix it until it is.
My Blog