Saidalo Saidalo - 1 year ago 38
MySQL Question

How to left join to select with statement

I have some tables:



I want select apropriate rows from first table after join to this result another column selected by apropiate id.

In other words I select all products on some cathegory and how many colors each product has.

For example

select *
from products p
where p.p_id = some_number

and join to this

select count(*)
from colors c
where c.p_id = p.p_id

Thanks in advance

Answer Source

Left join return all row of left table(products) even row's id are not match of right table. for more info click here

SELECT p.p_id, COUNT(*) AS colours_count
FROM products AS p
LEFT JOIN colors c 
ON c.p_id=p.p_id
GROUP BY p.p_id