help on sql statement

Forum Moderators: open

Message Too Old, No Replies

help on sql statement

a difficult sql query

coolclu3

8:43 am on Feb 13, 2009 (gmt 0)

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

9:53 am on Feb 13, 2009 (gmt 0)

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

LifeinAsia

4:42 pm on Feb 13, 2009 (gmt 0)

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

11:01 pm on Feb 13, 2009 (gmt 0)

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

11:17 pm on Feb 13, 2009 (gmt 0)

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

1:48 am on Feb 14, 2009 (gmt 0)

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

coolclu3

9:27 am on Feb 14, 2009 (gmt 0)

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