mysql query

ocallagh

Wondering if I could get some help on this.

I have three tables.

1) Categories
2) Content
3) CategoryContent relationship table (ContentId, CatId)

Content can be associated with multiple categories via the CategoryContent table.

I have over 30,000 rows in the content table and on average each piece of content is assoicated with 8 categories.

What is the best way for selecting content that appears in 2 categories. Say the user wants to view content from categories Cat1 AND Cat2. Because of the layout of the tables I am finding it a bit hard. Maybe I'm missing something or my hangover has affected me worse than I thought!

This works but it's ugly... and if the user selects up to 8 categories it's running 8 similar queries on the same table. Surely there is a quicker way to do it.

SELECT * FROM Content WHERE 
ContentId IN  (
       SELECT ContentId FROM CategoryContent WHERE CatId = 1
       ) 
AND 
ContentId IN  (
      SELECT ContentId FROM CategoryContent WHERE CatId = 2
      )

ocallagh

This also works but is probably even more of an insight into my hangover...

SELECT A.* FROM Content A WHERE 
A.ContentId IN (
      SELECT ContentId FROM CategoryContent WHERE CatId IN (1,2) 
      ) //this sub query returns either Cat1 or Cat2 which is too much content
AND 2 = (
      SELECT COUNT(1) FROM CategoryContent WHERE CatId IN (1,2) AND ContentId = A.ContentId GROUP BY ContentId
      ) //so this second sub query makes sure we have 2 documents

ocallagh

Just so this thread has an answer, here is the required query.

SELECT Content.* 
  FROM ( SELECT ContentID 
           FROM CategoryContent 
          WHERE CatID IN ( 1 , 2 )
         GROUP
             BY ContentID
         HAVING COUNT(*) = 2 ) AS qualified
INNER
  JOIN Content
    ON Content.ContentID = qualified.ContentID