I have this product_user table which has foreign key product in it.
I need to group and count all of the products from that table with the same IDs and get that product name so result of the query could be:
+------------+--------------+-------+ | product id | product name | count | +------------+----------------------+ | 36 | product 1 | 5 | +------------+----------------------+ | 22 | product 2 | 8 | +------------+--------------+-------+
I think I could use join table and one of many tries was:
select products.id - count(products.id) from products LEFT OUTER JOIN product_user ON product_user.product = products.id group by products.Id
I am new at writing SQL queries so any hint can be useful.
Advertisement
Answer
This:
select products.id - count(products.id)
Should be:
select products.id, products.name, count(product_user.id)
Optionally change this:
group by products.id
To this:
group by products.id, products.name