JOIN 3 Rows in 1 Table to 1 Row in another Table

Question

I need to create a recordset which Joins 3 rows from one table on 1 row from another table.

At present I have:

   SELECT * 
     FROM tb_product_sub_cat 
LEFT JOIN tb_products ON tb_product_sub_cat.category_id = tb_products.product_subcategory 
    WHERE tb_product_sub_cat.category_name = %s

I also need:

tb_product_sub_cat.category_id = tb_products.product_subcategory2 AND
tb_product_sub_cat.category_id = tb_products.product_subcategory3

I need to find all products that have 'stuff' in either/or all of the product categories.

Answer 1

Not completely clear what you want hear but this looks like a job for UNION:-

    SELECT * FROM tb_product_sub_cat LEFT JOIN tb_products ON tb_product_sub_cat.category_id = tb_products.product_subcategory WHERE tb_product_sub_cat.category_name = %s 
UNION
SELECT * FROM tb_product_sub_cat LEFT JOIN tb_products ON tb_product_sub_cat.category_id = tb_products.product_subcategory2 WHERE tb_product_sub_cat.category_name = %s
UNION
SELECT * FROM tb_product_sub_cat LEFT JOIN tb_products ON tb_product_sub_cat.category_id = tb_products.product_subcategory3 WHERE tb_product_sub_cat.category_name = %s

Answer 2

SELECT * FROM tb_product_sub_cat 
INNER JOIN tb_products 
ON tb_product_sub_cat.category_id =   tb_products.product_subcategory2 
AND tb_product_sub_cat.category_id = tb_products.product_subcategory3 
AND   tb_product_sub_cat.category_id =  tb_products.product_subcategory  
WHERE tb_product_sub_cat.category_name = %s

This is my interpretation of your scenario. Let me know if this works for you or if you need elaboration.

EDIT

SELECT * FROM tb_product_sub_cat 
LEFT JOIN tb_products 
ON tb_product_sub_cat.category_id =   tb_products.product_subcategory2 
AND tb_product_sub_cat.category_id = tb_products.product_subcategory3 
AND   tb_product_sub_cat.category_id =  tb_products.product_subcategory  
WHERE tb_product_sub_cat.category_name = %s

Answer 3

Table aliases will make your life easier:

   SELECT * 
     FROM tb_product_sub_cat t
LEFT JOIN tb_products tp ON t.category_id IN (tp.product_subcategory, tp.product_subcategory2, tp.product_subcategory3)
    WHERE t.category_name = %s