boreas boreas - 6 months ago 21
SQL Question

Mysql query with group by having

I have a table with 3 columns: id, sentence and language. So sentences can be either in English and German, IDs are assigned to sentences with same meaning but different languages, like

ID | sentence | language
1 | Hello | en
1 | Hallo | de
2 | Sorry | en


there could be sentences that exist only in one language. Now I want to find out all sentences that are available in both language, I can do with:

SELECT
*
FROM
`sentences`
WHERE
LENGTH(sentence) > 0
AND (language = 'en' OR language = 'de')
GROUP BY id
HAVING COUNT(language) = 2


and I get results of sentences only in German. Then I do

SELECT
*
FROM
sentences
WHERE
id IN (SELECT
id
FROM
`sentences`
WHERE
LENGTH(sentence) > 0
AND (language = 'en' OR language = 'de')
GROUP BY id
HAVING COUNT(language) = 2)


That should work but query takes forever. My question: is there any fancy way to do this?

Answer

INNER JOINS are faster than using IN clause

SELECT en.id, 
       en.sentence as en_sentence,
       de.sentence as de_sentence,
       en.language as en_language,
       de.language as de_language
FROM sentences en
INNER JOIN sentences de ON en.ID = de.ID AND en.language = 'en' AND de.language = 'de'
WHERE length(en.sentence) > 0
AND length(de.sentence) > 0