>SELECT Master.Numero >FROM (SELECT Master.Numero FROM Master >WHERE EXISTS (SELECT 1 FROM ManyToManyTable > INNER JOIN ChildTable ON ManyToManyTable.NoChildTable=ChildTable.Numero > WHERE Master.Numero=ManyToManyTable.NoMaster > HAVING COUNT(*)=SUM(CASE WHEN ChildTable.Enabled=1 THEN 1 ELSE 0 END)) >GROUP BY Master.Numero) Temp >INNER JOIN Master ON Temp.Numero=Master.Numero >If you want to make sure that all records are enabled, then
select M.Numero from Master M inner join ManyToManyTable MM on M.Numero = MM.NoMaster inner join ChildTable C on MM.NoChildTable = C.Numero group by M.Numero having min(case when C.Enabled = 1 then 1 else 0 end) = 1 -- means all records are enabled and sum(case when C.Enabled = 1 then 1 else 0 end) = count(C.Numero) -- total number of enabled records = total number of joined records