php – Incorrect work of SQL query when choosing the number of products in categories and subcategories

Question:

There are 3 tables:

  1. "goods" – goods.

  2. "grup_goods" – stores information about product groups.

  3. grup_link_goods – Links the goods table to the grup_goods table.

    The task is to display the categories and the number of products in them, I do this:

     SELECT gr_g.url AS url, gr_g.grup_img AS grup_img, gr_g.name_menu AS name_menu, gr_g.title AS title, IFNULL(COUNT(gr_l_g.id), 0) AS count FROM grup_goods AS gr_g LEFT JOIN grup_link_goods AS gr_l_g ON (gr_g.id = gr_l_g.id_grup) WHERE gr_g.id_part=.$id. GROUP BY gr_l_g.id_grup ORDER BY gr_g.id ASC

There would be nothing)), but the result is a bit different from what was expected: It turns out something like this:

name_menu | count (5 шт)
name_menu | count (3 шт)
name_menu | count (0 шт)

And you need:

name_menu | count (5 шт)
name_menu | count (3 шт)
name_menu | count (0 шт)
name_menu | count (0 шт)
name_menu | count (0 шт)

Only one category with the quantity of goods = 0 is displayed, and the rest with zero quantity disappear somewhere ((.

What could be the problem???

Answer:

@ 3per told you correctly about grouping, it needs to be done by the field of the main table (gr_g.id), and not the table used for LEFT JOIN, because you select all data from the main table, and not from the secondary one.

In fact, with such a query, which you now have, you select a set of different data from the secondary table (NULL, 1, 2, 3 …), and the values ​​of the main table that are not in the secondary are simply truncated and passed to NULL.

You only get one 0, because when you join the table, the gr_l_g.id_grup field is NULL and all zero values ​​are essentially grouped into this output line.

Scroll to Top