There are 3 tables:

  1. "goods" - goods.

  2. "grup_goods" - stores information about groups of goods.

  3. "grup_link_goods" - links the goods table with the grup_goods table.

    The task is to deduce the categories and quantity of the goods 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

All would be nothing)), but the result is a little not 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 a zero quantity disappears somewhere ((.

What could be the problem???

  • may need grouping on the gr_g table? - 4per
  • No, this is nothing to do with it, the number displays the correct, the values โ€‹โ€‹disappear = 0 - Alexey Glowatskiy

1 answer 1

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

In fact, with such a query that you have now, 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 table are simply cut off to NULL.

You only output one 0, because when joining a table, the gr_l_g.id_grup field is NULL and all null values โ€‹โ€‹are essentially grouped into this output string.

  • Thank you so much, that's right, everything is working out - Alexey Glowatskiy