Joining across join tables - SQL Server Forums

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)

Joining across join tables

Author

Topic

Maracatu
Starting Member

13 Posts

Posted - 2009-02-09 : 11:18:21

Hi
I have 2 tables. One is a table of keyword tags against articles - there can be many rows for any particular article depending on which keywords have been assigned. The other is a "Stored search" table which contains, for a given searchID, rows for all keyword tags that are to be searched for in a particular search. The statement below will bring back all articles that have been marked with any of the keywords in the search - but I want to return only articles that have been marked with all the keywords (A logical AND, rather than OR). Any ideas?

SELECT ark.ARTICLE_ID
FROM TBL_STORED_SEARCH ssc
INNER JOIN TBL_ARTICLE_KEYWORDS ark ON ssc.KEYWORD_ID = ark.KEYWORD_ID
WHERE (ssc.SEARCHID = @SEARCHID)

thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-09 : 11:22:43

is this what you want?

SELECT	ark.ARTICLE_ID
FROM	TBL_STORED_SEARCH ssc
INNER JOIN TBL_ARTICLE_KEYWORDS ark ON ssc.KEYWORD_ID = ark.KEYWORD_ID 
WHERE (ssc.SEARCHID = @SEARCHID)
GROUP BY ark.ARTICLE_ID
HAVING COUNT(DISTINCT ssc.KEYWORD_ID)=(SELECT COUNT(DISTINCT KEYWORD_ID ) FROM TBL_ARTICLE_KEYWORDS)

Maracatu
Starting Member

13 Posts

Posted - 2009-02-09 : 11:30:30

thanks but that's not quite what i need - that code appears to match the counts of keywords contained in a particular search with the total count of different keywords available from the article keywords table - i need to match all the keywords against a given article with all the keywords in a given search.

Maracatu
Starting Member

13 Posts

Posted - 2009-02-09 : 12:05:16

Inspired by your suggestion of group by, visakh - I experimented and I think this works...
SELECT ark.ARTICLE_ID
FROM TBL_STORED_SEARCH ssc
INNER JOIN TBL_ARTICLE_KEYWORDS ark ON ssc.KEYWORD_ID = ark.KEYWORD_ID
WHERE (ssc.SEARCHID = @SEARCHID)
GROUP BY ark.ARTICLE_ID
HAVING COUNT(DISTINCT ark.KEYWORD_ID)=(SELECT COUNT(DISTINCT ssc2.KEYWORD_ID ) FROM TBL_STORED_SEARCH ssc2 where ssc2.SEARCHID = @SEARCHID)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-09 : 12:23:44

Ok..if it works, good...but based on limited info you gave, i dont think i can suggest much more. some sample data would have made your scenario more clear.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources