Complex Search Query - PLEASE HELP!

• Psyonara

  SSC Enthusiast

  Points: 104

  More actions

  February 28, 2008 at 7:43 am

  #187129

  Hi there,

  I need a query to join 3 tables. Here is my setup:

  TABLE | FIELDS

  Groups | ID, Name

  Keywords | ID, GroupID, Keyword

  Search | ID, Keyword

  "Groups" and "Keywords" are linked one to many, with each group being assigned multiple keywords. "Search" holds a list of keywords that I want to search for. In particular, I need to find the groups that have ALL of the keywords in "Search" assigned to them (not just at least one). I've tried many different approaches, but cannot find a way to do this with a single query. All my attempts so far also return groups that only have a few of the keywords in "Search" assigned to them, but not ALL.

  Can anyone help? I am desperate to find a solution...

  Thanks!

  Helmut

• Mark Cowne

  One Orange Chip

  Points: 26977

  More actions

  February 28, 2008 at 8:15 am

  #783199

  Sample data would help...

  select g.ID,g.Name

  from Groups g

  where exists (select 1

  from Search s

  left outer join Keywords k on s.Keyword=k.Keyword

  and k.GroupID=g.ID

  having count(s.ID)=count(k.ID))

  ____________________________________________________

  Deja View - The strange feeling that somewhere, sometime you've optimised this query before

  How to get the best help on a forum

  http://www.sqlservercentral.com/articles/Best+Practices/61537

• Psyonara

  SSC Enthusiast

  Points: 104

  More actions

  February 28, 2008 at 8:53 am

  #783268

  THANK YOU SO MUCH!

  That is exactly what I need! This solves it 100%!