Query help: Recursive + joins

RGM · #1 (**permalink**) 02-21-11, 03:00

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_products` p) 

JOIN `QS_brands` b ON `p`.`brand_id` = `b`.`id`

WHERE `p`.`id` = 1 and p.visible = "yes"

r937 · #2 (**permalink**) 02-21-11, 05:59

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'

RGM · #3 (**permalink**) 02-21-11, 14:44

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 p 

INNER 

 JOIN QS_brands AS b 

   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 = 1 

  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_brands` b 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?

r937 · #4 (**permalink**) 02-21-11, 15:25

Quote:

Originally Posted by RGM

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?

RGM · #5 (**permalink**) 02-21-11, 15:32

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!