CODEWITHSUNDEEP
sqlt-sql
Matija karlovic
Matija karlovic

Reputation: 43

How to find records in a table that are related to all the records in another table

I have three tables:

FooBar is a relation table that contains collection of Foos and Bars that are related to one another, it has only two columns (FooId, BarId).

My code so far for getting all the Foo that relate to all the Bars out:

select 
    f.*
from 
    Foo f
where
    f.FooId IN 
         (
        SELECT fb.FooId
        FROM FooBar fb
        GROUP BY fb.FooId
        HAVING COUNT(*) = (SELECT COUNT(*) FROM Bar)
         )

There has to be more efficient way to write this. I could put total number of Bars in a SQL variable outside of outer select so it doesn't execute every time, but that's the only optimization that I can think of so far.

Upvotes: 0

Views: 225

Answers (1)

Kirill Polishchuk
Kirill Polishchuk

Reputation: 56172

Try this, it will return all the Foo that are related to all the Bar. It uses the exists operator:

select *
from @Foo f
where not exists(
    select 1
    from @Bar b
    left join @FooBar fb on fb.BarID = b.ID and fb.FooID = f.ID
    where fb.FooID is null
)

Sample data:

declare @FooBar table(BarID int, FooID int)
insert @FooBar values(1,1), (2,1), (3,1), (1,2), (2,2), (1,3), (2,3), (3,3)

declare @Bar table(ID int)
insert @Bar values(1), (2), (3)

declare @Foo table(ID int)
insert @Foo values(1), (2), (3)

Upvotes: 1

Related Questions