Page 1 of 1

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

Posted: Tue Oct 27, 2009 12:33 am
by Shellfish
Hi there, Hoping someone can help me. 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 = 'stuff'

I also need:

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

Is there a join which can do an AND for the JOINS?
I have a table of Product Sub-Categories, and a Products table with product_subcategory, product_subcategory2 & product_subcategory3
I need to find all products that have 'stuff' in either/or all of the product categories.
Thanks in advance, and I hope it's clear enough to understand

Regards, Warren

Re: MySQL - JOIN 3 Rows in 1 Table to 1 Row in another Table

Posted: Tue Oct 27, 2009 12:51 am
by Iainzor
JOINS can have multiple conditions:

Code: Select all

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