help on sql statement

a difficult sql query

[coolclu3]

Hi all,

I have 2 tables with the following fields

1. articles
(id, text, original_language)
PRIMARY KEY 'id'

2. article_translations(original_article_id, language, translated_text)
PRIMARY KEY ('original_article_id, language')

Suppose I have 4 languages available to be translated: 'en', 'de', 'fr' and 'it' (article_translations.language is of ENUM data type

- Each time a new article is created, a 'original_language' must be specified.
- Each time a new translation is created, a 'language' must be selected and this is apparently different from the article's original language

How can I find all the articles that have been translated to all the languages?

Regards

[topr8]

SELECT original_article_id,count(*) as numTranslations
FROM article_translations
WHERE numTranslations=4
GROUP BY original_article_id

[LifeinAsia]

You may need to use the following instead:

SELECT original_article_id,count(*) as numTranslations
FROM article_translations
GROUP BY original_article_id
HAVING count(*)=4

[topr8]

yeah, sorry ... LifeinAsia is of course correct, i wrote that reply during my morning coffe when i first woke up, sorry wasn't concentrating!

[LifeinAsia]

Don't feel bad- the came to me in a rare bout of clarity during a week of ODing of cold medicine... *hack* *cough*

[coolclu3]

Thanks, but this gives the following error:
#1054 - Unknown column 'numTranslations' in 'where clause'

[coolclu3]

Sorry, i was lookin at topr8's solution. I only saw his answer, (It's the firefox's cache)
LifeinAsia's solution works, thanks a lot guys.
Cheers