$query = "SELECT IFNULL(COUNT(c.id), 0) AS count, s.id, s.name FROM section_catalog s JOIN catalog c ON (s.id = c.id_cat) GROUP BY c.id_cat ORDER BY s.name ASC";

As a result, should display the following:

 name 1 (0 шт) name 2 (0 шт) name 3 (4 шт) name 4 (5 шт) name 5 (0 шт)

But displays only

 name 1 (0 шт) name 3 (4 шт) name 4 (5 шт)

Those are all records in which count>0 , and the first record with count 0 Broke the whole head, how to fix this query?

  • one
    At least the ER-diagram add to the question - Alexey Alybin
  • Everything is easier), there is a table with the ID and section name, and there is a table with the goods you need to display all sections and the number of goods. The problem is that if the goods there is no quantity is displayed - bajor
  • one
    try left join - Yura Ivanov
  • what is the query SELECT COUN(*) cnt, s.id, s.name FROM section_catalog s JOIN catalog c ON (s.id = c.id_cat) GROUP BY c.id_cat ORDER BY s.name ASC you did not like? - Opalosolo

1 answer 1

As Yura Ivanov wrote in his comment, you need left join (or left outer join - as you like):

 SELECT IFNULL(COUNT(c.id), 0) AS count, s.id, s.name FROM section_catalog s LEFT JOIN catalog c ON (s.id = c.id_cat) GROUP BY c.id_cat ORDER BY s.name ASC