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:
name NumberOfScenes NumberOfPictures NumberOfActors
sea 1 3 0
select T.name, NumberOfScenes, NumberOfPicture, NumberOfActors from Tags as T
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))
from Scene_tag where Tag_id = T.id
I think the most efficient way to do what you want moves the subqueries to the
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.