Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2005
    Posts
    2

    Unanswered: conditional query question

    I have the following table...

    +----+-------------+----------+
    | id | category_id | item_id |
    +----+-------------+----------+
    | 1 | 1 | 1 |
    | 2 | 4 | 1 |
    | 3 | 6 | 1 |
    | 4 | 4 | 2 |
    | 5 | 1 | 3 |
    | 6 | 4 | 3 |
    | 7 | 6 | 3 |
    | 8 | 3 | 4 |
    | 9 | 12 | 4 |
    | 10 | 14 | 4 |
    | 11 | 1 | 5 |
    | 12 | 1 | 6 |
    | 13 | 1 | 7 |
    | 14 | 5 | 8 |
    | 15 | 13 | 8 |
    +----+-------------+----------+

    I have a search that allows users to pick the categories they want to view items from but also to choose to not show items with other categories, is there any way in my query to stop an item from showing if it has both categories chosen, in other words, if the user wanted to view all the items from category 1 but not category 4 how could i make items 1 and 3 not show up in the results?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    select item_id
      from catitems
    group
        by item_id
    having sum(case when category_id = 1 then 1 else 0 end) > 0 
       and sum(case when category_id = 4 then 1 else 0 end) = 0
    results: item_ids 5,6,7
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2005
    Posts
    2
    Works great, Thanks

Posting Permissions

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