I want to get records from the product_spec_data table that are associated with products whose category_id is 5.
Please help me make a query to the database...
CodePudding user response：
All of the relationship are clearly laid out, a simple join across tables would works.
SELECT psd.*
FROM product_spec_data psd
INNER JOIN product_spec_values psv ON psd.id = psv.product_spec_data_id
INNER JOIN products prod ON psv.product_id = prod.id
INNER JOIN categories cat ON prod.category_id = cat.id
-- with category id = 5
WHERE cat.id = 5;
CodePudding user response：
I am not sure what do you mean by complex query. I guess your question is not complete. I think the query for your question (if it is T-SQL) will be as follows
select d.*
from product_spec_data d
left outer join product_spec_values v on d.id=v.product_spec_data_id
left outer join products p on v.product_id=p.id
where p.category_id=5
(I think in MySQL also above syntax will remain same, you may remove [outer] clause in MySQL)