SQL - Joining columns from the same table in a Query

Question

SOLVED ! See the answer bellow !

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.

I have 3 tables.

Hata and Icon contains images I want to link with Succes which contains texts

[Hata]

id, INTEGER, AUTO_INCREMENT, PRIMARY_KEY
hata Image
idLang, VARCHAR(5)

[Icon]

id, INTEGER, AUTO_INCREMENT, PRIMARY_KEY
icon, IMAGE
idPhrase, INTEGER

[Succes]

id, INTEGER, AUTO_INCREMENT, PRIMARY_KEY
idPhrase, INTEGER
titre, VARCHAR(25)
desc, VARCHAR(125)
idLang, VARCHAR(5)

Here is a sample showing how the Succes table looks like

+----+----------+-----------------+------------------+--------+
| 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  |
+----+----------+-----------------+------------------+--------+
...

The tables are now joined using this WHERE conditions

Icons.idPhrase = Succes.idPhrase AND Hata.idLang=Succes.idLang

Everything would be fine if there would be nothing specific in the Succes table.

In fact, for each Icon there are 3 sentences and the idPhrase links them but in the actual result set I somehow have redundancies.

Icon1|FlagIcon1|TitreLang1|DescLang1
Icon1|FlagIcon2|TitreLang2|DescLang2
Icon1|FlagIcon3|TitreLang3|DescLang3
Icon2|FlagIcon1|TitreLang1|DescLang1
Icon2|FlagIcon2|TitreLang2|DescLang2
Icon2|FlagIcon3|TitreLang3|DescLang3
...

What I'd like to achieve is the following (just the very first row):

In other words, it would be like I'd joined several queries together such as

SELECT icon FROM Icon

Joined with

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'

Joined With

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'

And so on... Just the problem of ensuring the links between tables (icon 1 with sentence 1)

Here's another sample on how it should (may) look like

+-------+-------+-------+-------+----------------+------------------+------------+-----------------+----------------+------------------+
| icon  | fEN   | fFR   | fJP   | tEN            | tFR              | tJA        | dEN             | dFR            | dJA              |
+-------+-------+-------+-------+----------------+------------------+------------+-----------------+----------------+------------------+
| <img> | <img> | <img> | <img> | Hello          | Salut            | 今日は      | Desc in English | Desc in French | Desc in Japanese |
+-------+-------+-------+-------+----------------+------------------+------------+-----------------+----------------+------------------+
| <img> | <img> | <img> | <img> | Goodbye        | Au revoir        | またね       | Desc in English | Desc in French | Desc in Japanese |
+-------+-------+-------+-------+----------------+------------------+------------+-----------------+----------------+------------------+
| <img> | <img> | <img> | <img> | You're welcome | Je vous en pries | どういたしまして | Desc in English | Desc in French | Desc in Japanese |
+-------+-------+-------+-------+----------------+------------------+------------+-----------------+----------------+------------------+
...

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.

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'

the message is

Statut SQL: HY000
Error Code: 1000

syntax error, unexpected $end, expecting BETWEEN or IN or SQL_TOKEN_LIKE

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.

Thank you for your availability and help.

Have you looked at using a UNION to join multiple select queries? It may not work for all of them, but for some of those it would be a good start. — James Black, Sep 11 '11 at 20:34
Thank you for your comment. As I wrote before I tried many things and UNION belongs to them. From what I saw **UNION does not combine columns but rows** and therefore you have to take care that datatypes for each column is the same. — 猫IT, Sep 11 '11 at 21:46
To do that is easy, just return a null for select statements that won't return that column, and you can build them up. — James Black, Sep 11 '11 at 23:24
I think I should try to use MySQL on Linux to test the queries just to see if the problem would be coming from Open Office Base or the query itself. I don't understand the NULL thing much. If I already can't add columns with LEFT JOIN. — 猫IT, Sep 12 '11 at 23:47
What I have done before is Select colA, colB, null FROM ... UNION Select null, null, colC FROM ... — James Black, Sep 13 '11 at 00:51

score 0 · Answer 1 · answered Sep 23 '11 at 01:40

I really don't get it. I've tried with MySQL and it does something like an exclusive join

mysql> SELECT titre AS tfr, titre AS ten, titre AS tjp FROM data WHERE idlang=1
    -> UNION
    -> SELECT null,titre AS ten, null FROM data WHERE idlang=2
    -> UNION
    -> SELECT null, null, titre as tjp FROM data WHERE idlang=3;
+------------------+------------------+------------------+
| tfr              | ten              | tjp              |
+------------------+------------------+------------------+
| Salut            | Salut            | Salut            |
| Au revoir        | Au revoir        | Au revoir        |
| Je vous en pries | Je vous en pries | Je vous en pries |
| NULL             | Hello            | NULL             |
| NULL             | Goodbye          | NULL             |
| NULL             | You're Welcome   | NULL             |
| NULL             | NULL             | Konnichiha       |
| NULL             | NULL             | Mata ne          |
| NULL             | NULL             | Douitashimashite |
+------------------+------------------+------------------+
9 rows in set (0.00 sec)

If in the 1st SELECT I do titre AS tfr, null, null the column headers get to null.

mysql> SELECT titre AS tfr, titre AS ten, titre AS tjp FROM data WHERE idlang=1
    -> UNION
    -> SELECT null,titre AS ten, null FROM data WHERE idlang=2
    -> UNION
    -> SELECT null, null, titre as tjp FROM data WHERE idlang=3;
+------------------+------------------+------------------+
| tfr              | NULL             | NULL             |
+------------------+------------------+------------------+
| Salut            | NULL             | NULL             |
| Au revoir        | NULL             | NULL             |
| Je vous en pries | NULL             | NULL             |
| NULL             | Hello            | NULL             |
| NULL             | Goodbye          | NULL             |
| NULL             | You're Welcome   | NULL             |
| NULL             | NULL             | Konnichiha       |
| NULL             | NULL             | Mata ne          |
| NULL             | NULL             | Douitashimashite |
+------------------+------------------+------------------+

It still doesn't looks like the result I want.

I need to concentrate on that data table to get all in one line but I keep wondering how to achieve this. In principle it is very simple but I can't translate that in SQL.

mysql> DESCRIBE data;
+-----------+-------------+------+-----+---------+----------------+
| Field     | Type        | Null | Key | Default | Extra          |
+-----------+-------------+------+-----+---------+----------------+
| id        | int(11)     | NO   | PRI | NULL    | auto_increment |
| id_phrase | int(11)     | YES  |     | NULL    |                |
| titre     | varchar(20) | YES  |     | NULL    |                |
| desc      | varchar(50) | YES  |     | NULL    |                |
| idicon    | int(11)     | YES  |     | NULL    |                |
| idlang    | int(11)     | YES  |     | NULL    |                |
+-----------+-------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)

Actually idicon is redundant with id_phrase (don't really need it so pretend it does not exist).

Thank you.

猫IT · Answer 2 · 2012-02-05T17:30:30.047

HERE ARE SOME PROPOSITIONS IF YOU WOULD MEET A RESEMBLING PROBLEM.

NB: The column names and table names may differ from the original question but the problem is the same.

I've been asking this question on another forum and here are 2 queries I tested and can certify are working with MySQL 5.5

Query 1 :

SELECT id_phrase
    , idicon
    , max(case idlang when 1 then titre end) AS tfr
    , max(case idlang when 1 then DESC  end) AS dfr
    , max(case idlang when 2 then titre end) AS ten
    , max(case idlang when 2 then DESC  end) AS den
    , max(case idlang when 3 then titre end) AS tjp
    , max(case idlang when 3 then DESC  end) AS djp
FROM DATA
    WHERE idlang IN (1, 2, 3)
GROUP BY id_phrase, idicon
ORDER BY id_phrase ASC

Query 2 :

SELECT t1.id_phrase, t1.idicon, t1.titre AS tfr, t1.descr AS dfr, t2.titre AS ten, t2.descr AS den, t3.titre AS tjp, t3.descr AS djp
FROM DATA AS t1
LEFT OUTER JOIN DATA AS t2
    ON t1.id_phrase=t2.id_phrase
LEFT OUTER JOIN DATA AS t3
    ON t1.id_phrase=t3.id_phrase
WHERE t1.idlang=1 AND t2.idlang=2 AND t3.idlang=3

You're welcome if these queries may help you.

Source (french)

SQL - Joining columns from the same table in a Query

2 Answers2