Results 1 to 8 of 8
  1. #1
    Join Date
    Feb 2011
    Posts
    29

    Unanswered: Query help: Recursive + joins

    I'm having a little trouble getting the data I need for related products.

    Given a product id of 1 I want to find all the related products (who's ID is in related_id) then get their full info from the products table and join with the brands table to get the name.

    To get just the product and brand without the related I've been using:

    PHP Code:
    SELECT `p`.*, `b`.`name` as brand_name 
    FROM 
    (`QS_productsp
    JOIN `QS_brandsb ON `p`.`brand_id` = `b`.`id`
    WHERE `p`.`id` = and p.visible "yes" 
    Attached Thumbnails Attached Thumbnails grab.jpg  

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT p.*
         , b.name as brand_name  
         , p2.id AS related_id
         , b2.name AS related_brand
      FROM QS_products AS p 
    INNER 
      JOIN QS_brands AS b 
        ON b.id = p.brand_id
    LEFT OUTER
      JOIN QS_related_products AS r
        ON r.product_id = p.id
    LEFT OUTER
      JOIN QS_products AS p2
        ON p2.id = r.related_id 
       AND p2.visible = 'yes'  
    LEFT OUTER
      JOIN QS_brands AS b2
        ON b2.id = p2.brand_id
     WHERE p.id = 1 
       AND p.visible = 'yes'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2011
    Posts
    29
    Thanks for the reply Rudy.

    I changed it a little to return the right results:

    PHP Code:
    SELECT 
          p2
    .*
        , 
    b2.name AS related_brand
     FROM QS_products 
    AS 
    INNER 
     JOIN QS_brands 
    AS 
       ON b
    .id p.brand_id
    LEFT OUTER
     JOIN QS_products_related 
    AS r
       ON r
    .product_id p.id
    LEFT OUTER
     JOIN QS_products 
    AS p2
       ON p2
    .id r.related_id 
      
    AND p2.visible 'yes'  
    LEFT OUTER
     JOIN QS_brands 
    AS b2
       ON b2
    .id p2.brand_id
    WHERE p
    .id 
      
    AND p.visible 'yes' 
    Note the p2.* is for brevity's sake.

    Looks quite laborious though? I'd be interested in a better solution if you have any opinions for one.

    The other option I had for this was a 'related' field in the products table with a csv line of id's (2,3,4,5,6), the query being:

    PHP Code:
    select p.*, `b`.`name` as brand_name  
    from QS_products p
    JOIN 
    `QS_brandsb ON `p`.`brand_id` = `b`.`id`
    where p.id in (2,3,4,5,6) and p.visible "yes"
    The 2,3,4,5,6 being a variables value. From searching here you seem to not recommend the IN() approach however?

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by RGM View Post
    The 2,3,4,5,6 being a variables value.
    which you could only have got from a previous query

    so you're running two queries, right? compared to my one?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Feb 2011
    Posts
    29
    Correct, I have the full row for the current product (id=1) before I run this one for related products.

    Sorry i should have made that clear. I run four in total:

    - current product row from 'QS_products'
    - related images
    - related options
    - and this one, related products


    This image verification is painful!

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by RGM View Post
    This image verification is painful!
    huh? image verification?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Feb 2011
    Posts
    29
    Us new posters have to suffer through an Image verification captcha to post..

    So would you recommend the IN() method or?

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by RGM View Post
    So would you recommend the IN() method or?
    sure, if you're okay with running separate queries, yes
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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