0

I have three tables:

  • Foo
  • Bar
  • FooBar

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.

Daniel DiPaolo
  • 55,313
  • 14
  • 116
  • 115
Matija karlovic
  • 43
  • 1
  • May I ask what the purpose of this program is? I read the title, and it vaguely reminded me of this question: http://stackoverflow.com/questions/3877178/using-god-to-monitor-unicorn-start-exited-with-non-zero-code-1 – bbosak Jul 16 '11 at 15:29
  • Maybe it's worth to change subject area? – Kirill Polishchuk Jul 16 '11 at 15:38
  • Actually your query gives pretty good execution plan, I wouldn't worry. – Magnus Jul 16 '11 at 15:55
  • @IDWMaster - Actual problem I have can be simplified to this, I just assumed this title would get me more views and thus answers than title with releases and customers :) – Matija karlovic Jul 16 '11 at 18:28

1 Answers1

1

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)
Daniel DiPaolo
  • 55,313
  • 14
  • 116
  • 115
Kirill Polishchuk
  • 54,804
  • 11
  • 122
  • 125
  • that solves the solution of getting "a girl record" if one exists, but what about the case when you want all matching girl records? – Brian Webster Jul 16 '11 at 15:25
  • @polishchuk thanks for comment but for large number of data (BoyGirl count over 3.000.000) this is very slow (ex. time: 43 sec) – Matija karlovic Jul 16 '11 at 18:53
  • @Matija The `where bg.GirlID is null` requires a Filter operation thats why. What is the time on your original query? If its bad maybe you should consider putting your inner query (needs some rearranging) in an indexed view. – Magnus Jul 16 '11 at 18:58
  • @Daniel DiPaolo, Subject area changed? :-) – Kirill Polishchuk Jul 18 '11 at 17:46
  • Yeah, I reopened and then changed the title to make it more clear, but then the question just looked weird so I went ahead and edited it to be more generic and then edited your post to match – Daniel DiPaolo Jul 18 '11 at 18:24