I am working with 3 tables sales, customer & product where I want to get all columns from the sales table and customer_name, customer age from customer table and product_name, category from the product table in the same result set. There's a condition that I have to use 2 join operations where one join will be inside subquery and the other outside subquery. Here's my query that's not working:
SELECT a.*,
b.customer_name,
b.age,
b.customer_id
FROM sales AS a
LEFT JOIN customer AS b on a.customer_id = b.customer_id
LEFT JOIN (SELECT product_id, product_name, category from product) as c
ON a.product_id = c.product_id
ORDER BY a.customer_id, b.customer_id;
Only the first join is working in this query.
select
a.*,
b.customer_name,
b.age,
b.customer_id ,
c.*
from sales as a
left join customer as b
on a.customer_id = b.customer_id
LEFT JOIN (SELECT product_id, product_name, category from product) as c
ON a.product_id = c.product_id
order by a.customer_id;