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!