MySQL-Query to get articles with only the chosen language

I'm developing a shop system and got problems with the search. Searching for articles containing languages or genders is no problem, but I'm struggling getting articles with only the selected one.

This is an example of the used tables:

articles

id   | int(11)  | Primary Key | auto_increment
name | tinytext |

languages

id   | int(11)  | Primary Key | auto_increment
lang | tinytext |

article_languages

article_id  | int(11) | Primary Key
language_id | int(11) | Primary Key

So I want now to know which articles have the language (fo开发者_开发技巧r example English), but not the others (German, Turkish, Spanish etc.). Tried a lot of things but never got the right result.

Is this the right Database structure? And if it is, how to SELECT the right rows. My brain is killing me...

Hope this information is enough.

Greetings iNaD

PS: Sry for my bad English :(

---

This might not be the best way to do it, but it works. My thought process was that we want to select articles that have only one language, so first we should find out how many languages each article has. I did this with by grouping results from article_languages by article_id and using the COUNT aggregate function to produce 'languages_count'.

You can't use a WHERE clause with a derived column (I think - please correct me if I'm wrong) so I wrapped that up as a sub-query and the outer query limits the results with a WHERE, and also does a JOIN to fetch the actual articles.

As a result, this query will return all articles that have only one language, regardless of what that language is (I hope that's what you wanted, if not refer to the other answers):

SELECT a.* FROM (SELECT article_id,language_id,COUNT(article_id) AS languages_count FROM article_languages GROUP BY article_id) AS al LEFT JOIN articles AS a ON al.article_id = a.id WHERE al.languages_count = 1;

---

For example:

SELECT a.* FROM articles as a, languages as l, article_languages as al WHERE l.id = al.language_id AND a.id = al.article_id AND l.lang = 'ENGLISH';

If you already know the language ID (1 in this example) it will be:

SELECT a.* FROM articles as a, article_languages as al WHERE a.id = al.article_id AND al.language_id = 1;