'How to select all entries in a table with a subquery counting entries in another table by id from the first query

I have two tables: albums and photos

albums

id name user_id
1 New Year 2022 2
2 Birthday Party 2
3 Wedding 2

For every album there are photos in photos table

photos

id album_id name
1 1 IMG_0754.JPG
2 2 IMG_0764.JPG
3 2 IMG_0654.JPG
4 3 IMG_1254.JPG
5 3 IMG_0054.JPG
6 3 IMG_0004.JPG

I need to select all albums by user_id (in this example by user_id 2) from albums table, and count all photos from photos table, so that I get something like

id name user_id photo_count
1 New Year 2022 2 1
2 Birthday Party 2 2
3 Wedding 2 3

Thanks!

sqlpostgresql


Solution 1:[1]

This is how I would write it with ms-sql. You may need to tweak it for postgresql.

SELECT a.id, a.name, a.user_id, count(*) as photo_count
FROM ALBUMS a
INNER JOIN PHOTOS p on a.id = p.id
GROUP BY a.id, a.name, a.user_id

Solution 2:[2]

I figured out the answer, which is

select albums.id, albums.name, albums.user_id,(select count(*) from photos where photos.album_id = albums.id) as photo_count from albums where user_id = 2

Solution 3:[3]

OK, after the advice of @Sonyck the solution would be

SELECT albums.id, albums.name, albums.user_id, count(*) as photo_count 
FROM albums 
JOIN photos ON (photos.album_id = albums.id)
WHERE user_id = 2
GROUP BY albums.id, albums.name, albums.user_id

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 user18042248
Solution 2 Andrey Yaroshenko
Solution 3 Andrey Yaroshenko

Related Questions