JOIN 3 Rows in 1 Table to 1 Row in another Table
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 J开发者_运维知识库OIN 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.
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
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
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
精彩评论