I have a three tables: topics
, sentences
, and vocabulary
. Sentences and vocabulary both have a belongsTo
topic_id
, but not all topics necessarily have both vocabulary and sentences. I want to get a count of all topics that have both sentences and vocabulary.
I have it working if I do one table at a time:
select
*
from (
select
t.id as topic_id,
count(v.id) total_vocabulary
from topics t
left join vocabulary v on (v.topic_id = t.id)
where v.locale_id = 1
group by t.id
order by t.id
) as topics_with_vocabulary
where total_vocabulary > 0
The output is accurate:
Same for sentences:
But I want to do it performantly for both sentences and vocabulary.
If I do it the following way, it’s counting the # of vocabulary for both sentences and vocabulary (which makes sense since it’s counting total rows), but not unique counts of total_sentences and total_vocabulary separately.
select
*
from (
select
t.id as topic_id,
count(s.id) as total_sentences,
count(v.id) as total_vocabulary
from topics t
left join sentences s on (s.topic_id = t.id)
left join vocabulary v on (v.topic_id = t.id)
where s.locale_id = 1
and v.locale_id = 1
group by t.id
order by t.id
) as topics_with_sentences
where total_sentences > 0
or total_vocabulary > 0
Advertisement
Answer
To count of all topics that have both sentences and vocabulary, require that there exists rows in both child tables:
select count(*)
from topics t
where exists (select * from vocabulary where topic_id = t.id)
and exists (select * from sentences where topic_id = t.id)