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

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'
);