SQL Question: Selecting different language versions based on availability I have the following situation: I have a table that contains entries with language versions (German and English) as part of the key (the other part being a "name" field). However, not always both versions are available - sometimes, e.g. only a German version exists. I want to build a select statement so that I get all English ones that are available, and all the German ones for the entries that have no English version. The resulting table should not contain any duplicates in the "name" field.

Currently, I've come up with this query:

SELECT * FROM table WHERE lang = 'E'
UNION
SELECT * FROM table WHERE lang = 'D' 
                      AND name NOT IN (SELECT name FROM table WHERE lang = 'E')


This works, but it seems very complicated and I'm afraid it doesn't perform very well. Also, if I want additional WHERE clauses on the result I have to build another SELECT * around it, using the above statement as a temp table.

Is there a better way to do this? Thanks for your help in advance!
You can create a query to get the two language as two column, as translation, and use COALESCE to get the value that is present.
If you have one language that have all the data you'll want to use it as the anchor and write a LEFT JOIN (if word is the field with the data)

SELECT COALESCE(e.word, d.word) word
FROM   table D 
       LEFT JOIN table E ON e.lang = 'E' and d.lang = 'D' and e.name = d.name


otherwise a FULL JOIN is needed

SELECT COALESCE(e.word, d.word) word
FROM   table D 
       FULL JOIN table E ON e.lang = 'E' and d.lang = 'D' and e.name = d.name


Edit
As ypercube pointed out MySQL don't support the FULL JOIN, but it can be simulated

SELECT COALESCE(e.word, d.word) word
FROM   table D 
       LEFT JOIN table E ON e.lang = 'E' and d.lang = 'D' and e.name = d.name
UNION
SELECT COALESCE(e.word, d.word) word
FROM   table D 
       RIGHT JOIN table E ON e.lang = 'E' and d.lang = 'D' and e.name = d.name