title: SQL - Joining columns from the same table in a Query
body: <p>SOLVED ! See the answer bellow !</p>
  
  <p>Before I explain my problem I want to apologise for those who would feel this question is too long but I feel like I must give some details to make things the clearer possible. Though, the problem is simple to understand it is not that simple to me to implement.</p>
  
  <p>I have 3 tables.</p>
  
  <p>Hata and Icon contains images I want to link with Succes which contains texts</p>
  
  <p><strong>[Hata]</strong></p>
  
  <pre><code>id, INTEGER, AUTO_INCREMENT, PRIMARY_KEY
  hata Image
  idLang, VARCHAR(5)
  </code></pre>
  
  <p><strong>[Icon]</strong></p>
  
  <pre><code>id, INTEGER, AUTO_INCREMENT, PRIMARY_KEY
  icon, IMAGE
  idPhrase, INTEGER
  </code></pre>
  
  <p><strong>[Succes]</strong></p>
  
  <pre><code>id, INTEGER, AUTO_INCREMENT, PRIMARY_KEY
  idPhrase, INTEGER
  titre, VARCHAR(25)
  desc, VARCHAR(125)
  idLang, VARCHAR(5)
  </code></pre>
  
  <p>Here is a sample showing how the Succes table looks like</p>
  
  <pre><code>+----+----------+-----------------+------------------+--------+
  | id | idPhrase | titre           | desc             | idLang |
  +----+----------+-----------------+------------------+--------+
  |  1 |        1 | Hello           | Desc in English  | en-GB  |
  +----+----------+-----------------+------------------+--------+
  |  2 |        1 | Salut           | Desc in French   | fr-FR  |
  +----+----------+-----------------+------------------+--------+
  |  3 |        1 | 今日は           | Desc in Japanese | ja-JP  |
  +----+----------+-----------------+------------------+--------+
  |  4 |        2 | Goodbye         | Desc in English  | en-GB  |
  +----+----------+-----------------+------------------+--------+
  |  5 |        2 | Au revoir       | Desc in French   | fr-FR  |
  +----+----------+-----------------+------------------+--------+
  |  6 |        2 | またね            | Desc in Japanese | ja-JP  |
  +----+----------+-----------------+------------------+--------+
  |  7 |        3 | You're welcome  | Desc in English  | en-GB  |
  +----+----------+-----------------+------------------+--------+
  |  8 |        3 | Je vous en prie | Desc in French   | fr-FR  |
  +----+----------+-----------------+------------------+--------+
  |  9 |        3 | どういたしまして      | Desc in Japanese | ja-JP  |
  +----+----------+-----------------+------------------+--------+
  ...
  </code></pre>
  
  <p>The tables are now joined using this <strong>WHERE</strong> conditions</p>
  
  <p><em>Icons.idPhrase = Succes.idPhrase <strong>AND</strong> Hata.idLang=Succes.idLang</em></p>
  
  <p>Everything would be fine if there would be nothing specific in the Succes table.</p>
  
  <p>In fact, for each Icon there are 3 sentences and the idPhrase links them but in the actual result set I somehow have redundancies. </p>
  
  <pre><code>Icon1|FlagIcon1|TitreLang1|DescLang1
  Icon1|FlagIcon2|TitreLang2|DescLang2
  Icon1|FlagIcon3|TitreLang3|DescLang3
  Icon2|FlagIcon1|TitreLang1|DescLang1
  Icon2|FlagIcon2|TitreLang2|DescLang2
  Icon2|FlagIcon3|TitreLang3|DescLang3
  ...
  </code></pre>
  
  <p>What I'd like to achieve is the following (just the very first row):</p>
  
  <p><code>Icon1|FlagIcon1|TitreLang1|DescLang1|FlagIcon2|TitreLang2|DescLang2|FlagIcon3|TitreLang3|DescLang</code></p>
  
  <p>or
  <code>Icon1|FlagIcon1|FlagIcon2|FlagIcon3|TitreLang1|DescLang1|TitreLang2|DescLang2|TitreLang3|DescLang3</code></p>
  
  <p>or even
  <code>Icon1|FlagIcon1|FlagIcon2|FlagIcon3|TitreLang1|TitreLang2|TitreLang3|DescLang1|DescLang2|DescLang3</code></p>
  
  <p>In other words, it would be like I'd joined several queries together such as</p>
  
  <pre><code>SELECT icon FROM Icon
  </code></pre>
  
  <p>Joined with</p>
  
  <pre><code>SELECT Hata.hata AS fEN, Succes.titre AS tEN, Succes.desc AS dEN
  FROM Hata, Succes
  WHERE Hata.idLang=Succes.idLang AND Succes.idLang='en-GB'
  </code></pre>
  
  <p>Joined With</p>
  
  <pre><code>SELECT Hata.hata AS fFR, Succes.titre AS tFR, Succes.desc AS dFR
  FROM Hata, Succes
  WHERE Hata.idLang=Succes.idLang AND Succes.idLang='fr-FR'
  </code></pre>
  
  <p>And so on...
  Just the problem of ensuring the links between tables (icon 1 with sentence 1)</p>
  
  <p>Here's another sample on how it should (may) look like</p>
  
  <pre><code>+-------+-------+-------+-------+----------------+------------------+------------+-----------------+----------------+------------------+
  | icon  | fEN   | fFR   | fJP   | tEN            | tFR              | tJA        | dEN             | dFR            | dJA              |
  +-------+-------+-------+-------+----------------+------------------+------------+-----------------+----------------+------------------+
  | &lt;img&gt; | &lt;img&gt; | &lt;img&gt; | &lt;img&gt; | Hello          | Salut            | 今日は      | Desc in English | Desc in French | Desc in Japanese |
  +-------+-------+-------+-------+----------------+------------------+------------+-----------------+----------------+------------------+
  | &lt;img&gt; | &lt;img&gt; | &lt;img&gt; | &lt;img&gt; | Goodbye        | Au revoir        | またね       | Desc in English | Desc in French | Desc in Japanese |
  +-------+-------+-------+-------+----------------+------------------+------------+-----------------+----------------+------------------+
  | &lt;img&gt; | &lt;img&gt; | &lt;img&gt; | &lt;img&gt; | You're welcome | Je vous en pries | どういたしまして | Desc in English | Desc in French | Desc in Japanese |
  +-------+-------+-------+-------+----------------+------------------+------------+-----------------+----------------+------------------+
  ...
  </code></pre>
  
  <p>I've browsed for SQL reference to try many things but they don't seem to do what I expect (CONCATENATE, UNION, etc...)
  I also tried the following query but it gives me an error message.</p>
  
  <pre><code>SELECT Icon.icon, Hata.hata AS fEN,Hata.hata AS fFR,Hata.hata AS fJA
      ,'FR'.'titre', 'FR'.'desc'
      ,'JA'.'titre', 'JA'.'desc'
      ,'UK'.'titre', 'UK'.'desc'
  FROM Hata, Icon
  LEFT JOIN Succes AS FR ON 'FR'.'idLang' = 'Hata'.'idLang' AND 'FR'.'idLang' = 'fr-FR'
  LEFT JOIN Succes AS JA ON 'JA'.'idLang' = 'Hata'.'idLang' AND 'FR'.'idLang' = 'ja-JP'
  LEFT JOIN Succes AS UK ON 'UK'.'idLang' = 'Hata'.'idLang' AND 'FR'.'idLang' = 'en-GB'
  </code></pre>
  
  <p>the message is</p>
  
  <pre><code>Statut SQL: HY000
  Error Code: 1000
  
  syntax error, unexpected $end, expecting BETWEEN or IN or SQL_TOKEN_LIKE
  </code></pre>
  
  <p>but it seems my syntax is good according to sample I've found even on StackOverflow.
  I must also specify that I'm using OpenOffice Base and my purpose is publishing a document. Maybe there is something specific to OOo such as LEFT JOIN not implemented but the code get coloured so I think it should be fine.</p>
  
  <p>Thank you for your availability and help.</p>