Distinct Count with data from another table

I have 4 tables

All ID related things are ints and the rest are texts. I want to count the number of albums the user is tagged at so if a user is tagged in album1 once album2 once and album3 once it will show 3 and if more in any of them it will still show 3. I tried to do:

SELECT COUNT(DISTINCT ALBUM_ID) FROM PICTURES WHERE ID=(SELECT PICTURE_ID FROM TAGS WHERE USER_ID=userId);

But this returned 1 although it was supposed to return 3 and the same happened without DISTINCT. How can I get the amount? EDIT: I want to check only one user(I have the user’s ID and name)

Answer

You must join users with LEFT joins to tags and pictures and aggregate:

SELECT u.id, u.name, COUNT(DISTINCT p.album_id) counter
FROM users u
LEFT JOIN tags t ON t.user_id = u.id
LEFT JOIN pictures p ON p.id = t.picture_id
GROUP BY u.id, u.name

If you want the result for a specific user only:

SELECT u.id, u.name, COUNT(DISTINCT p.album_id) counter
FROM users u
LEFT JOIN tags t ON t.user_id = u.id
LEFT JOIN pictures p ON p.id = t.picture_id
WHERE u.id = ?
GROUP BY u.id, u.name -- you may omit this line, because SQLite allows it

Or with a correlated subquery:

SELECT u.id, u.name, 
       (
         SELECT COUNT(DISTINCT p.album_id)
         FROM tags t INNER JOIN pictures p 
         ON p.id = t.picture_id
         WHERE t.user_id = u.id 
       ) counter
FROM users u
WHERE u.id = ?

Replace ? with the id of the user that you want.