debute debute - 2 months ago 8
MySQL Question

Get total records of children from connected table

I'm using MariaDB 5.5, but for this solution it would be same as for MySQL. I have two tables, first one contains galleries and second one contains info about files within each gallery. This is example of the table

gallery
:

+----+-------+-----+
| id | name | ... |
+----+-------+-----+
| 1 | test1 | ... |
| 2 | test2 | ... |
| 3 | test3 | ... |
| 4 | test4 | ... |
+----+-------+-----+


This is example of the table
gallery_items
:

+----+------+------------+-----+
| id | file | gallery_id | ... |
+----+------+------------+-----+
| 1 | img1 | 3 | ... |
| 2 | img2 | 2 | ... |
| 3 | img3 | 2 | ... |
| 4 | img4 | 1 | ... |
+----+------+------------+-----+


So I tried this code:

SELECT gallery.*, COUNT(gallery_items.id) AS items FROM gallery JOIN gallery_items WHERE gallery_items.gallery_id = gallery.id;


Well, I'm not really good with databases, so this is why I'm asking for help. This is my expected result:

+----+-------+-------+-----+
| id | name | items | ... |
+----+-------+-------+-----+
| 1 | test1 | 1 | ... |
| 2 | test2 | 2 | ... |
| 3 | test3 | 1 | ... |
| 4 | test4 | 0 | ... |
+----+-------+-------+-----+

Answer

you will need to GROUP BY in order for a COUNT to work

SELECT gallery.*, COUNT(gallery_items.id) AS items FROM gallery 
LEFT JOIN gallery_items ON gallery_items.gallery_id = gallery.id
GROUP BY gallery.id, gallery.name
Comments