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.
2
Answers
There are two issues in your query:
GROUP BY
clause. It’s better to move it inside theGROUP BY
clause to avoid subtle mistakes.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 theGROUP BY
clause. The former solution is preferred, as the second one would lead to incorrect result (and would not make sense as well).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:
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:
Check the demo here.
Further references:
ONLY_FULL_GROUP_BY
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
orEXISTS
clause. I am usingIN
for its simplicity and because we only want to select data from the grouper table: