SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    SitePoint Zealot w1nk5's Avatar
    Join Date
    Aug 2009
    Location
    Ontario, Canada
    Posts
    115
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Return Single Row when Product is in multiple categories.

    Ok. I have a products table, a categories table and a productcategories table.

    How can I get a row from the products table when the product is in Category A and Category B.

    He is some sample SQL code my search component is generating for a search for a product that is in 2 categories.

    Code:
    SELECT list.id,list.name,list.price,image.image 
    FROM wp_wpsc_product_list AS list 
    LEFT JOIN wp_wpsc_product_images AS image 
    ON list.image = image.id 
    LEFT JOIN wp_wpsc_item_category_assoc AS prodcat 
    ON list.id = prodcat.product_id 
    LEFT JOIN wp_wpsc_productmeta AS meta 
    ON list.id = meta.product_id 
    WHERE list.publish=1 
    AND list.active=1 
    AND prodcat.category_id=19 
    AND prodcat.category_id=11 
    AND list.price BETWEEN 751 AND 1000 
    AND meta.meta_value = 'Chatham'
    This is incorrect as it does not return any rows in the DB when there is a product in category 19 and 11 and all that meets all the criteria in the WHERE clause.

    I obviously have either the joins or where clause structured incorrectly as the prodcat.category_id = 19 and prodcat.category_id = 11 are 2 seperate rows in the productcategory table. Anyways, I would really appreciate some help.

    Cheers

  2. #2
    SitePoint Addict
    Join Date
    May 2003
    Location
    sitepoint
    Posts
    247
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'm still new at MySQL, but I ran into a similar problem, my syntax might be wrong, but you could try this. If not wait for someone that knows more, lol, just trying to possibly help.

    Code MySQL:
    SELECT list.id,list.name,list.price,image.image 
    FROM wp_wpsc_product_list AS list 
    LEFT JOIN wp_wpsc_product_images AS image 
    ON list.image = image.id 
    LEFT JOIN wp_wpsc_item_category_assoc AS prodcat 
    ON list.id = prodcat.product_id 
    LEFT JOIN wp_wpsc_productmeta AS meta 
    ON list.id = meta.product_id 
    WHERE list.publish=1 
    AND list.active=1  
    AND list.price BETWEEN 751 AND 1000 
    AND meta.meta_value = 'Chatham'
    AND prodcat.category_id IN (19, 11) 
    GROUP BY list.id
    HAVING COUNT(*) = 2;
    Half way to nowhere

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,015
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    atomicnuke, your HAVING COUNT(*)=2 solution is excellent

    but there may be a problem

    the SELECT clause doesn't match the GROUP BY clause, thus invoking an obscure mysql extension to standard sql, which allows the query to run and return only one row per list.id, with an indeterminate image if there is more than one image

    in fact, if a product has two images but is in only one category, then i think the query might return a false positive

    wink, is there any way to restrict the selection to a single image? say, the one with the latest date or something?

    by the way, those joins should all be INNER JOINs
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Zealot w1nk5's Avatar
    Join Date
    Aug 2009
    Location
    Ontario, Canada
    Posts
    115
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by atomicnuke View Post
    I'm still new at MySQL, but I ran into a similar problem, my syntax might be wrong, but you could try this. If not wait for someone that knows more, lol, just trying to possibly help.

    Code MySQL:
    SELECT list.id,list.name,list.price,image.image 
    FROM wp_wpsc_product_list AS list 
    LEFT JOIN wp_wpsc_product_images AS image 
    ON list.image = image.id 
    LEFT JOIN wp_wpsc_item_category_assoc AS prodcat 
    ON list.id = prodcat.product_id 
    LEFT JOIN wp_wpsc_productmeta AS meta 
    ON list.id = meta.product_id 
    WHERE list.publish=1 
    AND list.active=1  
    AND list.price BETWEEN 751 AND 1000 
    AND meta.meta_value = 'Chatham'
    AND prodcat.category_id IN (19, 11) 
    GROUP BY list.id
    HAVING COUNT(*) = 2;
    The 'IN' clause you are using replaces multiple OR statements.

    For example, in stead of prodcat.category_id = 19 OR prodcat.category_id = 11 you could simply use prodcat.category_id IN (19,11).

    I, however, need to replace a combination of multiple AND and OR statements for the prodcat.category_id column. For example, lets say where in prodcat.category_id = 2 AND (prodcat.categoy = 1 OR prodcat.category =3)

  5. #5
    SitePoint Zealot w1nk5's Avatar
    Join Date
    Aug 2009
    Location
    Ontario, Canada
    Posts
    115
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    atomicnuke, your HAVING COUNT(*)=2 solution is excellent

    but there may be a problem

    the SELECT clause doesn't match the GROUP BY clause, thus invoking an obscure mysql extension to standard sql, which allows the query to run and return only one row per list.id, with an indeterminate image if there is more than one image

    in fact, if a product has two images but is in only one category, then i think the query might return a false positive

    wink, is there any way to restrict the selection to a single image? say, the one with the latest date or something?

    by the way, those joins should all be INNER JOINs
    Why INNER join instead of LEFT join?

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,015
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    because there will be no unmatched rows

    why did you think you needed LEFT join instead of INNER?
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,015
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by w1nk5 View Post
    For example, lets say where in prodcat.category_id = 2 AND (prodcat.categoy = 1 OR prodcat.category =3)
    Code:
    HAVING COUNT(CASE WHEN category = 2 THEN 'ok' END) = 1
       AND COUNT(CASE WHEN category IN (1,3) THEN 'ok' END) > 0
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Zealot w1nk5's Avatar
    Join Date
    Aug 2009
    Location
    Ontario, Canada
    Posts
    115
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    Code:
    HAVING COUNT(CASE WHEN category = 2 THEN 'ok' END) = 1
       AND COUNT(CASE WHEN category IN (1,3) THEN 'ok' END) > 0
    I don't get it. What's with the THEN 'ok'?

  9. #9
    SitePoint Zealot w1nk5's Avatar
    Join Date
    Aug 2009
    Location
    Ontario, Canada
    Posts
    115
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hey Rudy, just realized who you are.

    I have your book Simply SQL, I'm gonna use it now to try figure this out.

    Thanks for your help.

    Cheers

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,015
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by w1nk5 View Post
    I don't get it. What's with the THEN 'ok'?
    it is countable

    it could actually have been any non-null value

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


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
  •