MySQL how to join on same table including missing rows

Question

I have a table with text in various language. Its defined like this:

Id|Language|Text

EXAMPLE DATA

0, ENU, a
0, DAN, b
1, ENU, c
2, ENU, d
2, DAN, e
3, ESP, f
3, ENU, g

Language and Id form the key.

Now I want to extract all texts in a langauge (lets say english) and have the coorosponding text in another language (lets say danish) shown in the column next to. So the result should be:

0, a, b
1, c, 
2, d, e
3, g

I know I can do a join like this:

SELECT t1.Id, t1.Text AS "ENU", t2.Text AS "DAN" table as t1 
JOIN table as t2 ON (t1.Id= t2.Id) 
WHERE t1.Langauge = "ENU" AND t2.Language = "DAN";

But this does not include the missing rows (ie row id=1 and id=3). How to do this?

* UPDATE **** I get suggestion to use LEFT JOIN but I cant get it working. Maybe because my table layout is a bit different than in the simplified question above. My table is defined as this:

Language|MPageId|MFieldId|MParagraph|MText

Where Language,MPageId,MFieldId,MParagraph forms the key

I tried this:

SELECT t1.MPageId, t1.MFieldId, t1.MParagraphId, t1.MText, t2.MText FROM main as t1 LEFT JOIN main as t2 ON (t1.MPageId = t2.MPageId AND t1.MFieldId = t2.MFieldId AND t1.MParagraphId = t2.MParagraphId) WHERE t1.MLanguage = 'ENU' AND t2.MLanguage = 'DAN'

score 2 · Accepted Answer · edited Feb 28 '12 at 09:20

2

SELECT t1.Id, t1.Text AS "ENU", t2.Text AS "DAN" FROM table as t1 
LEFT JOIN table as t2 ON (t1.Id= t2.Id AND t2.Language = "DAN")
WHERE t1.Langauge = "ENU"

edited Feb 28 '12 at 09:20

Muleskinner

14,150
19
58
79

answered Feb 24 '12 at 14:52

Pentium10

204,586
122
423
502

I did try LEFT JOIN but it is not giving me what I want. Maybe because my database i a bit different layed out than in my question. Updated my question. – Muleskinner Feb 24 '12 at 15:06

DRapp · Answer 2 · 2014-04-09T10:56:04.587

1

You do need the left join... but the "AND" clause for "DAN"ish would be applied AT the LEFT JOIN, and not in the WHERE clause... The where clause implies an INNER JOIN

SELECT 
      t1.Id, 
      t1.Text AS "ENU", 
      t2.Text AS "DAN" 
   from
      YourTable t1
         LEFT JOIN YourTable t2 
            ON t1.Id= t2.Id
           AND t2.Language = "DAN"
   where
      t1.Langauge = "ENU"

edited Apr 09 '14 at 10:56

answered Feb 24 '12 at 15:11

DRapp

47,638
12
72
142

score 0 · Answer 3 · answered May 19 '15 at 09:06

select Id,
MAX(case when LanguageS='ENU' then Text  else null end  ) as A,
MAX( case when LanguageS<>'ENU' then Text  else null end ) as B
from LAN
GROUP BY 1

Id      A           B
0       a           b
1       c           ?
2       d           e
3       g           f

score 0 · Answer 4 · answered Feb 24 '12 at 14:52

0

You want a Left Join: http://www.tizag.com/mysqlTutorial/mysqlleftjoin.php

answered Feb 24 '12 at 14:52

Scott Hunter

48,888
12
60
101

Thanks, I did try Left Join, but it is not giving me what I want, please check my update – Muleskinner Feb 24 '12 at 15:10

MySQL how to join on same table including missing rows

4 Answers4