get unique record counts of two joined tables

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

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)

Advertisement

Answer