SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Member
    Join Date
    Mar 2009
    Posts
    5
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Having a tough time with subquery

    I have two tables in question. I have a product table which contains a product_id. I also have a categories table containing an id, a product_id, a category_id, and the category_name.

    This has been setup in two different tables to allow for a product to be a member of multiple categories. A query on the categories table will yield something like

    1 | 20000 | 15 | electronics
    2 | 20000 | 30 | ipod
    3 | 20000 | 32 | blue ray
    4 | 21000 | 15 | electronics
    5 | 22000 | 15 | electronics


    The query I want to perform will select all product_id's where the category_id is electronics but the category_id must also not have an entry for ipod. So choosing all electronics that are not ipods.

    I've tried a subquery with the IN clause but the runtime of that query is far too large due to the size of the dtatabase.

    Your help is greatly appreciated!

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    you have only those two tables? not three?

    your table design badly needs revision

    you should have a products table, a categories table (containing just one row per category), and a product_categories table, to record which categories a product belongs to

    it seems you have mooshed the last two tables into one

    consequently the query gets confused -- the only way that you can have a situation where "the category_id must also not have an entry for ipod" is if there are no ipods at all

    however, i think i still understand what you want
    Code:
    SELECT product_id
      FROM categories AS electronics
    LEFT OUTER
      JOIN categories AS ipods
        ON ipods.product_id = electronics.product_id
     WHERE electronics.category_id = 15
       AND ipods.product_id IS NULL
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member
    Join Date
    Mar 2009
    Posts
    5
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I do have that structure. I have a categories lookup table just containing the information on what a category is and what it has (ie it's name and it's id). However, that is ancillary to this current situation and since the query in question should not need to query the lookup table since I intrinsically know what category_id I want and what category_id I don't want.

    I mashed those two tables together for the convenience of my example.

    I just used your query and I'm getting 0 rows...

    Thanks for your reply and help so far.

  4. #4
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,510
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Code:
    SELECT product_id
      FROM categories AS electronics
    LEFT OUTER
      JOIN categories AS ipods
        ON ipods.product_id = electronics.product_id
       AND ipods.category_id = 30
     WHERE electronics.category_id = 15
       AND ipods.product_id IS NULL

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    sorry, i wrote the query but neglected to test it

    try this --
    Code:
    SELECT electronics.product_id
      FROM categories AS electronics
    LEFT OUTER
      JOIN categories AS ipods
        ON ipods.product_id = electronics.product_id
       AND ipods.category_id = 30
     WHERE electronics.category_id = 15
       AND ipods.product_id IS NULL
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Member
    Join Date
    Mar 2009
    Posts
    5
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    This looks good and is returning what I want it to return.

    I want to be able to "ignore" more than one category in the same fashion that we "ignored" ipods from the selection. I tried an AND after ipods.category_id = 30 including the second id of the category for exclusion, but returned every row.

  7. #7
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,510
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    The category_id can't be 30 AND something else. You'd have to use OR, or even better: IN
    Code:
    SELECT electronics.product_id
      FROM categories AS electronics
    LEFT OUTER
      JOIN categories AS ipods
        ON ipods.product_id = electronics.product_id
       AND ipods.category_id IN (30, 45, 99)
     WHERE electronics.category_id = 15
       AND ipods.product_id IS NULL

  8. #8
    SitePoint Member
    Join Date
    Mar 2009
    Posts
    5
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ah, very nice. Thanks r937 and guido for your help. This solves it!


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
  •