Complex Search Query - PLEASE HELP!

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Complex Search Query - PLEASE HELP!

Author

Topic

whever
Starting Member

4 Posts

Posted - 2008-02-28 : 09:37:38

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-28 : 09:59:12

Hope this is what you want:-

SELECT g.Name
FROM Group g
CROSS JOIN Search s
LEFT OUTER JOIN Keyword k
ON k.GroupID = g.ID
AND k.keyword=s.keyword
GROUP BY g.Name
HAVING SUM(CASE WHEN k.keyword IS NULL THEN 1 ELSE 0 END) =0

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-02-28 : 10:19:18

or perhaps this:


select   [groupid]
from     [keywords]
group by [groupid]
having count(*) = (select count(*) from [search])

Be One with the Optimizer
TG

whever
Starting Member

4 Posts

Posted - 2008-02-28 : 10:56:19

That solves my problem perfectly! I modified it slightly to return the fields I want etc, and it WORKS!
Thanks a million!

quote:

Originally posted by visakh16

Hope this is what you want:-

SELECT g.Name
FROM Group g
CROSS JOIN Search s
LEFT OUTER JOIN Keyword k
ON k.GroupID = g.ID
AND k.keyword=s.keyword
GROUP BY g.Name
HAVING SUM(CASE WHEN k.keyword IS NULL THEN 1 ELSE 0 END) =0

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources