CODEWITHSUNDEEP
sqldatabasesqlite
Беннито
Беннито

Reputation: 27

I need to build multitable sqlite query

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

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

enter image description here

enter image description here

enter image description here

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.

Upvotes: 0

Views: 59

Answers (2)

WilliamK
WilliamK

Reputation: 1772

A user is owner of albums; Each album has many pictures; A picture has many tags; A user can tag himself in any picture;

Now, the question is: how many albums a user has pictures tagged?

(203 is the USER_ID.)

SELECT COUNT(DISTINCT A.ID) AS CNT 
FROM 
  ALBUMS A INNER JOIN PICTURES P ON P.ALBUM_ID = A.ID
  INNER JOIN TAGS T ON T.PICTURE_ID = P.ID
WHERE
  T.USER_ID = 203

Upvotes: 0

snatchysquid
snatchysquid

Reputation: 1352

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.

Upvotes: 1

Related Questions