Filtering by count and specific value from a related table

Question

Let's say we have a table called 'grouper'

id	column_a
1	value
2	value

And a table called 'translation' that contains following columns

id	locale	text	grouper_id
1	en	some en text	1
2	es	some es text	1
3	fr	some fr text	1
4	en	some en text	2

As we can see 'grouper' has one-to-many relation with 'translation'

What I want is to query only for those groupers that have only one related translation and that translation has to have 'en' locale. (i.e. grouper with id '2' because it has only one related translation with id '4' and this translation has 'en' locale).

I'm trying to do something like this:

SELECT gruper.id, 
       grouper.column_a 
FROM       grouper 
INNER JOIN translation 
        ON grouper.id = translation.grouper_id 
GROUP BY grouper.id 
HAVING COUNT(translation.id) = 1

And returned data is correct but locale is not considered in this case. The thing is, I don't know how to add the 'locale' condition correctly.

If I add it as WHERE translation.locale = 'en' the result will be incorrect since I will get all groupers with 'en' locale present in translations no matter how many related translations the grouper has.

And if I add the condition to HAVING clause like that HAVING COUNT(translation.id) = 1 AND translation.locale = 'en' MySQL throws an error:

Unknown column 'translation.locale' in 'having clause'.

Any help is much appreciated.

Answer 1

There are two issues in your query:

• "grouper.column_a" is neither aggregated, nor found within the GROUP BY clause. It's better to move it inside the GROUP BY clause to avoid subtle mistakes.
• "translation.locale" is not recognized as a field inside the HAVING clause, that's because this clause is activated only after the aggregation has already been carried out, for this reason translation doesn't exist anymore. In order to solve this issue, you either need to aggregate the same field, or add it to the GROUP BY clause. The former solution is preferred, as the second one would lead to incorrect result (and would not make sense as well).

SELECT grouper.id, 
       grouper.column_a 
FROM       grouper 
INNER JOIN translation 
        ON grouper.id = translation.grouper_id 
GROUP BY grouper.id, grouper.column_a
HAVING COUNT(translation.id) = 1 AND MAX(translation.locale) = 'en'

If you know that each translation cannot be repeated within your partitions, as it seems from your shared sample data, you could even reduce conditions to one only, by checking when sum of translation.locale = en is 1 as follows:

HAVING SUM(CASE WHEN translation.locale = 'en' THEN 1 ELSE -1 END) = 1

This would ensure that the only case when this sum gets 1 is when there's one translation.locale = 'en' and no other translations (count rows = 1).

Output:

id	column_a
2	value

Check the demo here.

---

Further references:

• Error related to only_full_group_by when executing a query in MySql
• Official docs on ONLY_FULL_GROUP_BY

Answer 2

You can phrase it like this: I want those grouper IDs for which both the minimum and the maximum locale is 'en'.

You can do this with a join or with an IN or EXISTS clause. I am using IN for its simplicity and because we only want to select data from the grouper table:

SELECT *
FROM grouper 
WHERE id IN
(
  SELECT grouper_id
  FROM translation 
  GROUP BY grouper_id
  HAVING MIN(locale) = 'en' AND MAX(locale) = 'en'
);

Answer 3

You can use inner join to join a list of groupers that only have one translation, which should be en.

select g.*
from grouper g
inner join (
  select grouper_id
  from translation
  group by grouper_id
  having count(*) = 1 and sum(locale='en') = 1 
) as s on s.grouper_id = g.id