sql - I need to build multitable sqlite query

Question

I really can't find a way to build the right request. That's my database design:

I need to find the number of albums that the user tagged in when my input is USER_ID. for example:

user Keni with ID 203 tagged in pictures with id 101, 103, 104 all these pictures located in different albums, so if i have the id 203, my output should be 3.

标签： sqldatabasesqlite

Answer 1

You can try this:

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

replace the 201 with the desired ID.