Good day. The following tables are available.

Leaf

+----+--------+ | id | name | +----+--------+ | 1 | first | +----+--------+ | 2 | second | +----+--------+

PLACE

 +----+---------+------+ | id | list_id | summ | +----+---------+------+ | 1 | 1 | 100 | +----+---------+------+ | 2 | 1 | 200 | +----+---------+------+

I need to select all the fields from the LIST table so that the table has the name LIST and the sum PLACE . About the table should get this

 +----+--------+------+ | id | name | summ | +----+--------+------+ | 1 | first | 300 | +----+--------+------+ | 2 | second | 0 | +----+--------+------+

I can’t imagine how to do this, because if you select a sample from PLACE , then those LIST that do not have PLACE will not get there. Otherwise, I have no idea how to summarize PLACE.summ

2 answers 2

 SELECT l.id, l.name, sum(p.summ) AS summ FROM LIST AS l LEFT JOIN PLACE AS p ON l.id=p.list_id GROUP BY l.id

    Helped union In this example, the request will be as follows

     # первый запрос - делаем выборку всех LIST в которых есть PLACE (SELECT l.id, l.name, SUM(p.summ) as summ FROM place p left join list l on l.id = p.list_id group by l.id) union # второй запрос - делаем выборку всех LIST в которых нет PLACE и задаем summ = 0 (SELECT l.id, l.name, 0 as summ FROM list l left outer join place p on l.id = p.list_id where p.id is null)
    • You complicate things. left join and so does what you need. You simply mixed up the tables in the left join. The table in which there are all the entries should be left ie first. Or you can use right join, which has the required table on the right: SELECT l.id, l.name, coalesce(SUM(p.summ),0) as summ FROM place p RIGHT join list l on l.id = p.list_id group by l.id - Mike