Post reply

Complex Search Query - PLEASE HELP!

  • 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

  • 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

  • February 28, 2008 at 8:53 am

    #783268

    THANK YOU SO MUCH!

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

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply