Saidalo Saidalo - 2 months ago 5
MySQL Question

How to left join to select with statement

I have some tables:

products
--------------------------
|p_id|col|col...|...|cat_id|


colors
-----------
|id|p_id|url|


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

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
Comments