Curtwagner1984 Curtwagner1984 - 1 month ago 10
SQL Question

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?

Answer

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.