Skip to content
Advertisement

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:

The output is accurate:

enter image description here

Same for sentences:

enter image description here

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.

enter image description here

Advertisement

Answer

To count of all topics that have both sentences and vocabulary, require that there exists rows in both child tables:

User contributions licensed under: CC BY-SA
7 People found this is helpful
Advertisement