3

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!

  • Which DBMS and version? – AakashM May 22 '14 at 9:24
  • 1
    Sorry, forgot that. I'm using MySQL V5.6. – basshaaren May 22 '14 at 9:34
1

you could choose a lot of ways. There is the one:

With tbl as (select distinct name from table)
Select coalesce(t1.name, t2.Name), coalesce(t1.field1, t2.fields) as field1....etc.
from tbl
 left join table t1 on tbl.NAME = t1.NAME and t1.lang = 'E'
 left join table t2 on tbl.NAME = t2.NAME and t2.lang = 'G'
|improve this answer|||||
1

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
|improve this answer|||||
  • OP commented they use MySQL (so no FULL JOIN available). – ypercubeᵀᴹ May 22 '14 at 11:30
  • @ypercube I wrote my answer as a general idea before the OP set the specific DB, still you're right and I will add the FULL JOIN form for MySQL – Serpiton May 22 '14 at 11:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.