CODEWITHSUNDEEP
mysqlsql
Thaw karahba
Thaw karahba

Reputation: 3

How do I join multiple tables into one with MySQL?

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.

Upvotes: 0

Views: 544

Answers (3)

deepak varshney
deepak varshney

Reputation: 1

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

Upvotes: 0

StevieG
StevieG

Reputation: 8709

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 
)

Upvotes: 1

Alexander Sulfrian
Alexander Sulfrian

Reputation: 3563

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;

Upvotes: 0

Related Questions