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!
CodePudding user response：
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
CodePudding user response：
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
CodePudding user response：
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