SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Member
    Join Date
    Oct 2010
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Query to select several Categories at Once

    I have three tables:- items, category & itemscategory
    Each item can have several categories associated with it.

    Currently I have a query which allows you to select one category, which will output all items associated with that category.
    What Iím trying to achieve is to be able to select several categories at once

    So far I have come up with

    SELECT itemname, COUNT(categoryid) AS Total
    FROM items LEFT JOIN itemscategory
    ON items.id = itemsid
    WHERE categoryid IN ('1', '2', '3', '4')
    GROUP BY items.id
    HAVING Total = '4'

    This query appears to work, but before I attempt to incorporate it into my PHP Code, I would just like some confirmation that itís the best solution to the problem.

    Many Thanks

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by LoonyOnTheBus View Post
    I would just like some confirmation that itís the best solution to the problem.
    you neglected to elaborate what the actual problem is

    that query will return only those items that belong to all categories selected

    by the way, please don't put quotes around numbers that are to be compared against numeric columns

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member
    Join Date
    Oct 2010
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sorry if I was unclear, will attempt to expand.

    Each item in a table can have one or more categories assigned to it.
    At the moment I have a query which can search on one choosen category, the query outputs all items containing that category.
    The query looks like

    SELECT itemname
    FROM items INNER JOIN itemscategory
    ON items.id = itemsid
    WHERE categoryid = '$categoryid'

    What I'm trying to achieve is the ability to search for more then one category at a time. So the query will only return items that contain the choosen categories.

    Hope that helps.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    that's what i thought you meant

    the query you gave in post #1 does, as you said, produce the expected results

    and yes, it is the best solution to the problem
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Member
    Join Date
    Oct 2010
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    That's great,
    Thanks for the confirmation.

    I can now look to include the query into my PHP Code.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •