How to make kind of IF in SQL query

Question

I have two tables, that need to be joined. They look like this

enter image description here

I need to join them so I have all the data from both tables and then display the information like this - IF there is German translation, display ONLY German. If there is no German, display English (Which is available for all the objects)

Im using MySql and expected output would be entry names of ids 1,2,3 on german (if there is german 1,2,3) or names on english with same ids, if there is no german translation in the database — , Mar 12 '13 at 19:32
No, im not linking anything. there is only this one table and there are objects which dont have german translation so i need to use english. but if one object have both eng and german, they will have same ID and i need to pick german one — , Mar 12 '13 at 19:34
Well Mr. Trent, go and charge it somewhere else. This is a place for helping eachother. I made several atempts, most of which failed. So i guess no point of writing my failed attempts here ... — , Mar 12 '13 at 20:27

score 3 · Answer 1 · answered Mar 12 '13 at 19:29

3

select  eng.entryid
,       coalesce(ger.name, eng.name)
from    YourTable eng
left join
        YourTable ger
on      ger.entryid = eng.entryid
        and ger.lang = 'ger'
where   eng.lang = 'eng'

answered Mar 12 '13 at 19:29

Andomar

232,371
49
380
404

1

I dont have two different tables...all the data is in the 1 table with both english and german translations or only english one – Mar 12 '13 at 19:35
2

The table is used twice in a self-join. But it's still one table called `YourTable`. The left side of the self-join is aliased as `eng` and the right side as `ger`. – Andomar Mar 12 '13 at 19:42

Jakub Kania · Accepted Answer · 2013-03-12T20:07:33.350

1

SELECT f1.id,f1.lang,f1.name
FROM foo AS f1
LEFT JOIN foo AS f2
ON f1.id=f2.id AND f1.lang<>f2.lang AND f2.lang='ger'
WHERE f2.id IS NULL

http://sqlfiddle.com/#!2/a0a75/9

If there are more than just english and german language the last line should be changed to

WHERE f2.id IS NULL AND (f1.lang='ger' OR f1.lang='eng')

edited Mar 12 '13 at 20:07

answered Mar 12 '13 at 19:35

Jakub Kania

15,665
2
37
47

1

Nice approach, currently fails if you add a [third language](http://sqlfiddle.com/#!2/7f936/1/0) – Andomar Mar 12 '13 at 19:41
1

@Andomar Yes it does,could be fixed by changing the last line to`WHERE f2.id IS NULL AND (f1.lang='ger' OR f1.lang='eng')` but the question doesn't really talk about third language so I didn't think it would be a problem. If there are more languages it should probably look entirely diffrent and have preference for that other language too. Thanks for your input, edited. – Jakub Kania Mar 12 '13 at 20:06
Hey man, Im rly not that good at sql, so I would have to ask you 1 more question. Can you do the same thing, but if we have 2 tables, that needs to be joined. they would look like this: CREATE TABLE foo (`id` int, `lang` varchar(3), `name` varchar(16)) ; INSERT INTO foo (`id`, `lang`, `name`) VALUES (1, 'eng', 'Prio 1 tasks'), (2, 'eng', 'Prio 2 tasks'), (1, 'ger', 'Prio 1 Ausfgaben') ; CREATE TABLE foo2 (`id` int, `parent_id` int) ; INSERT INTO foo (`id`, `parent_id`) VALUES (3,1 ), (4,1 ), (5,2 ) ; – Mar 12 '13 at 20:09
I'm afraid I don't understand what parent_id is or how do you want to join it. Like this: http://sqlfiddle.com/#!2/bf9c7/2 ? – Jakub Kania Mar 12 '13 at 20:19
This is a database from which i form a treeView. So parent ID is an id of a parent node. If it's 0, than it's the prime node – Mar 12 '13 at 20:22
@StefanBrankovic I'm not really how it's connected to this question. If you want to use it with a hierarchical query you sure can, at worst you can just put it in brackets and use as a subquery. If you want to know how to do a hierarchical query you can check this: http://stackoverflow.com/a/8111762/2115135 – Jakub Kania Mar 12 '13 at 20:28
I need to make a treeView looking something like this: http://tinypic.com/r/5v7ghh/6 If you can notice, first 6 master-nodes, are the nodes with the same ID but in diferent language, german and english (as you can see from the database i put above). Now, i need to join the two tables and filter the german translations and show only them, like you did before, but using single table. Have I made myself more clear now ? – Mar 12 '13 at 20:36
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/26057/discussion-between-stefan-brankovic-and-jakub-kania) – Mar 12 '13 at 20:41

score 1 · Answer 3 · answered Mar 12 '13 at 19:44

1

Try:

select entry_id,
       case max(lang)
           when 'ger' then max(case lang when 'ger' then name end)
           else max(name)
       end as trans_name
from your_table
group by entry_id

answered Mar 12 '13 at 19:44

1

This would fail with the addition of Zwahili as a language – Andomar Mar 12 '13 at 21:53
1

@Andomar: True, but there's no indication that the OP wants to deal with more than two languages, let alone *how* he would want to deal with more than two. (The simplest answer would be simply to include a `where lang in ('ger', 'eng')` clause, but from the question even that doesn't appear to be necessary.) – Mar 13 '13 at 06:48

How to make kind of IF in SQL query

3 Answers3