How do I join multiple tables into one with MySQL?

Question

I have three tables (deb, dos, char) and I'm looking for an SQL query to get the result table as shown in the following:

table: deb
+----+-------------+--------+
| id | label       | dos_id |
+----+-------------+--------+
|  1 | right       |      1 | 
|  2 | left        |      2 | 
+----+-------------+--------+

table:char
+----+-------------+--------+
| id | name        | dos_id |
+----+-------------+--------+
|  1 | jack        |      1 | 
|  2 | thaw        |      1 |
|  3 | lbaz        |      2 |
|  4 | amar        |      3 |
+----+-------------+--------+

table:dos
+----+-------------+
| id | color       |
+----+-------------+
|  1 | black       | 
|  2 | white       |
+----+-------------+


result table
+----+-------------+--------+
| id | color       | value  |
+----+-------------+--------+
|  1 | black       |  right | 
|  1 | black       |  jack  |
|  1 | black       |  thaw  |
|  2 | white       |  left  |
|  2 | white       |  lbaz  |
|  2 | white       |  amar  |
+----+-------------+--------+

I tried joining left and right but It didn't work.

What I'm looking for is that for each dos line I get corresponding char and deb values in other column.

Maybe making a row select query in one could help. I don't know.

Please help me and thanks.

StevieG · Accepted Answer · 2011-09-15 09:52:11Z

1

You need to run 2 seperate queries and combine the results together into a single resultset. You can do this using the UNION operator like this:

SELECT * FROM 
(
  SELECT dos.id id, dos.color color, deb.label value
  FROM dos INNER JOIN deb on dos.id = deb.dos_id
  UNION
  SELECT dos.id id, dos.color color, char.name value
  FROM dos INNER JOIN char on dos.id = char.dos_id
)
ORDER BY id

EDIT:

SELECT * 
FROM 
( 
  SELECT dos.id AS id, dos.date_facture AS facture, deb.libelle AS lbl, '' AS nom
  AS lbl FROM jos_tktransit_dossier dos INNER JOIN jos_tktransit_deboure deb ON dos.id = deb.id_dossier 
  UNION 
  SELECT dos.id AS id, dos.date_facture AS facture, '' AS lbl, charg.nom_charge AS nom 
  FROM jos_tktransit_dossier dos INNER JOIN jos_tktransit_charge_assistance charg ON dos.id = charg.id_dossier 
)

edited Sep 15, 2011 at 9:52

answered Sep 15, 2011 at 9:12

StevieG

8,7391 gold badge26 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Thaw karahba Over a year ago

i got this "1248 - Every derived table must have its own alias" and this is my query SELECT * FROM ( SELECT dos.id AS id, dos.date_facture AS facture, deb.libelle AS lbl FROM jos_tktransit_dossier AS dos INNER JOIN jos_tktransit_deboure AS deb ON dos.id = deb.id_dossier UNION SELECT dos.id AS id, dos.date_facture AS facture, charg.nom_charge AS nom FROM jos_tktransit_dossier AS dos INNER JOIN jos_tktransit_charge_assistance AS charg ON dos.id = charg.id_dossier )

StevieG Over a year ago

that wont work because you have different field names in the two queries. They must be the same. In my example, both queries return (id, color, value), but the queries in your comment return (id, facture, lbl) and (id, facture, nom). lbl != nom, so the UNION won't work.

StevieG Over a year ago

Not if you want them in the same column. If you want this then you must use the same alias as in the first answer. Otherwise you can put them in seperate columns. I've edited my answer to demonstrate this.

Alexander Sulfrian · Accepted Answer · 2011-09-15 09:12:40Z

0

Maybe something like:

SELECT dos.id, color, deb.label as value  FROM dos, deb WHERE dos_id = dos.id
UNION SELECT dos.id, color, char.name as value FROM dos, char WHERE dos_id = dos.id;

answered Sep 15, 2011 at 9:12

Alexander Sulfrian

3,5731 gold badge18 silver badges9 bronze badges

1 Comment

Thaw karahba Over a year ago

i got this "1248 - Every derived table must have its own alias" and this is my query SELECT * FROM ( SELECT dos.id AS id, dos.date_facture AS facture, deb.libelle AS lbl FROM jos_tktransit_dossier AS dos INNER JOIN jos_tktransit_deboure AS deb ON dos.id = deb.id_dossier UNION SELECT dos.id AS id, dos.date_facture AS facture, charg.nom_charge AS nom FROM jos_tktransit_dossier AS dos INNER JOIN jos_tktransit_charge_assistance AS charg ON dos.id = charg.id_dossier )

Mike G · Accepted Answer · 2012-03-27 04:59:26Z

0

Try the following:

(select d.id,d.color,c.name from dos d , char1 c where d.id=c.dos_id) union (select d.id,d.color,e.label from dos d, deb e where d.id= e.dos_id)

This should output the following:

ID  COLOR   NAME
1   black   jack
1   black   right
1   black   thaw
2   white   lbaz
2   white   left

...but it doesn't show the values in exactly the right order:

+----+-------------+--------+
| id | color       | value  |
+----+-------------+--------+
|  1 | black       |  right | 
|  1 | black       |  jack  |
|  1 | black       |  thaw  |
|  2 | white       |  left  |
|  2 | white       |  lbaz  |
| *2 | white       |  amar* |
+----+-------------+--------+

edited Mar 27, 2012 at 4:59

Mike G

7665 silver badges19 bronze badges

answered Sep 17, 2011 at 15:50

deepak varshney

1

Collectives™ on Stack Overflow

How do I join multiple tables into one with MySQL?

3 Answers 3

3 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related