user3217816 user3217816 - 3 months ago 9
MySQL Question

How to join four tables in MySQL/PHP

I have these four tables:

Items_a (item_id, name, cat_id, sub_cat_id)
Items_b (id, name2, cat_id, sub_cat_id)
Category (cat_id, cat_name)
Sub_category (sub_cat_id, sub_name, cat_id)


I want to join these table and trying following query but its not working:

SELECT * FROM(
SELECT * FROM(
SELECT * FROM items_c AS c
JOIN category as t ON c.cat_id=t.cat_id
) as ct
JOIN sub_category as s ON ct.sub_cat_id=s.sub_cat_id
) as cts
JOIN items_nc AS n ON cts.sub_cat_id=s.sub_cat_id


Can someone help me please?

Answer

try this:

SELECT *
FROM category c
INNER JOIN sub_category sc on c.cat_id = sc.cat_id
INNER JOIN items_a a on a.cat_id = c.cat_id AND a.sub_cat_id = sc.sub_cat_id
INNER JOIN items_b b on b.cat_id = c.cat_id AND b.sub_cat_id = sc.sub_cat_id
Comments