There is a table of products:

product_id | product_name | ... -------------------------------- 1 | name 1 | ... 2 | name 2 | ... 3 | name 3 | ...

There is a table of likes where product_id and user_id

  like_id | product_id | user_id -------------------------------- 1 | 1 | 1 2 | 2 | 1 3 | 1 | 2

That is, the first product has two likes, the second one, the third has no likes.

How can I calculate and display it in the products table and if there are no likes, output an empty field?

  • LEFT JOIN, GROUP BY and COUNT (). - Akina
  • @Akina 30 minutes can not integrate using GROUP BY . Can you tell with this? - MegaRoks
  • one
    select p.product_id, p.product_name, count(user_id) as lcount from products as p left join likes as l on (l.product_id = p.product_id) group by p.product_id, p.product_name

1 answer 1

 SELECT p.product_id,p.product_name,COUNT(*) as cnt FROM products p LEFT JOIN likes l USING(product_id) GROUP BY p.product_id,p.product_name
  • Do you have exactly GROUP BY l.product_id ? I think it should be GROUP BY p.product_id - MegaRoks
  • 2
    Normally, DBMSs do not allow use in the select list * and other fields that are not specified in the grouping. The using operator will also not work everywhere, but rather in few places. - teran
  • one
    corrected, USING did not meet that did not work - sterx