Advertisement
If you have a new account but are having problems posting or verifying your account, please email us on hello@boards.ie for help. Thanks :)
Hello all! Please ensure that you are posting a new thread or question in the appropriate forum. The Feedback forum is overwhelmed with questions that are having to be moved elsewhere. If you need help to verify your account contact hello@boards.ie
Hi there,
There is an issue with role permissions that is being worked on at the moment.
If you are having trouble with access or permissions on regional forums please post here to get access: https://www.boards.ie/discussion/2058365403/you-do-not-have-permission-for-that#latest

mysql query

  • 26-09-2009 4:06pm
    #1
    Registered Users, Registered Users 2 Posts: 3,141 ✭✭✭


    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
          )
    


Comments

  • Registered Users, Registered Users 2 Posts: 3,141 ✭✭✭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
    


  • Registered Users, Registered Users 2 Posts: 3,141 ✭✭✭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
    


Advertisement