CODEWITHSUNDEEP
sqlsqlitejoin
Curtwagner1984
Curtwagner1984

Reputation: 2088

Sqlite: Query to count the number of times a row is referenced in a 'through' table

I have tables for Actor,Picture, Scene and Tag. Actors, Pictures, and Scenes can have multiple Tags and vice versa. So the relationship between each pair is represented in a through tables that looks like this:

TABLE Scene_tag:
    Scene_id: int
    Tag_id: int

TABLE Picture_tag:
    Picture_id: int
    Tag_id: int

TABLE Actor_tag:
    Actor_id: int
    Tag_id: int

(This is just pseudo code and not actual table definition)

I'm trying to create an SQLite query that would list all the tags and the number of times they are referenced in each through table. For example if the tag 'Sea' is used in 3 pictures and 1 scene the desired output would be:

name    NumberOfScenes    NumberOfPictures    NumberOfActors
sea     1                 3                   0

What I got so far is:

select T.name, NumberOfScenes, NumberOfPicture, NumberOfActors from Tags as T
join(
select * from (
select count(*) AS NumberOfScenes from Scene_tag  where Tag_id = 73)
 join (select count(*) AS NumberOfPicture from Picture_tag  where Tag_id = 73)
 join (select count(*) AS NumberOfActors from Actor_tag  where  Tag_id = 73))

The problem I'm having is I can't figure out how to reference an Id from the first select in the join section so instead of the fixed 73 it would be something like from Scene_tag where Tag_id = T.id

Can someone help me achieve this? Or offer an alternative approach?

Upvotes: 1

Views: 61

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270613

I think the most efficient way to do what you want moves the subqueries to the select clause:

select t.name,
       (select count(*) from Scene_tag st where st.Tag_id = t.Tag_Id) as NumberOfScenes,
       (select count(*) from Picture_tag pt where pt.Tag_id = t.Tag_id) as NumberOfPicture,
       (select count(*) from Actor_tag ac where ac.Tag_id = t.Tag_id) as NumberOfActors
from Tags t
where t.tag_id = 73;

If you put the subqueries in the FROM clause, you would need to aggregate the subqueries by Tag_id to do what you want. However, if you are only looking for a single tag, then this is a lot of unnecessary work. The correlated subqueries in the FROM will only do the counting for tag id 73.

Note: For performance, you want indexes on Tag_Id in all four tables.

Upvotes: 2

Related Questions