LEFT JOIN HOW A NEW COLUMN?

Question

I am trying to make multiple LEFT JOINs in the same column of a table. I need LEFT JOIN "table2.words" with "table1.color" and "table2.words" with "table1.food". How can I do it? and can I do this by forcing the left link "table2.words" to a new column?

My SQL code:

SELECT table1.id, table1.color, table2.words FROM table1 LEFT JOIN table2 ON table1.color=table2.id LEFT JOIN table2 ON table1.food=table2.id 

table1:

 -------------------------------- | id | color | food | -------------------------------- | 1 | 1 | 3 | | 2 | 1 | 4 | | 3 | 1 | 3 | | 4 | 1 | 4 | | 5 | 2 | 3 | | 6 | 2 | 4 | | 7 | 2 | 3 | | 8 | 2 | 4 | -------------------------------- 

table2:

 --------------------------- | id | words | --------------------------- | 1 | yellow | | 2 | blue | | 3 | cookies | | 4 | milk | --------------------------- 

What am I trying to deduce:

 ---------------------------------------- | id | colorname | foodname | ---------------------------------------- | 1 | yellow | cookies | | 2 | yellow | milk | | 3 | yellow | cookies | | 4 | yellow | milk | | 5 | blue | cookies | | 6 | blue | milk | | 7 | blue | cookies | | 8 | blue | milk | ---------------------------------------- 

Note. I cannot change table structures.

Answer 1

 SELECT table1.id, table2_A.words colorname, table2_B.words foodname FROM table1 LEFT JOIN table2 table2_A ON table1.color=table2_A.id LEFT JOIN table2 table2_B ON table1.food=table2_B.id; 

Data examples

 mysql> drop database if exists supercoolville; Query OK, 2 rows affected (0.06 sec) mysql> create database supercoolville; Query OK, 1 row affected (0.00 sec) mysql> use supercoolville; Database changed mysql> create table table1 -> ( -> id int not null auto_increment, -> color int, -> food int, -> primary key (id) -> ); Query OK, 0 rows affected (0.06 sec) mysql> insert into table1 (color,food) values -> (1,3),(1,4),(1,3),(1,4), -> (2,3),(2,4),(2,3),(2,4); Query OK, 8 rows affected (0.06 sec) Records: 8 Duplicates: 0 Warnings: 0 mysql> create table table2 -> ( -> id int not null auto_increment, -> words varchar(20), -> primary key (id) -> ); Query OK, 0 rows affected (0.05 sec) mysql> insert into table2 (words) values -> ('yellow'),('blue'),('cookies'),('milk'); Query OK, 4 rows affected (0.07 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> select * from table1; +----+-------+------+ | id | color | food | +----+-------+------+ | 1 | 1 | 3 | | 2 | 1 | 4 | | 3 | 1 | 3 | | 4 | 1 | 4 | | 5 | 2 | 3 | | 6 | 2 | 4 | | 7 | 2 | 3 | | 8 | 2 | 4 | +----+-------+------+ 8 rows in set (0.01 sec) mysql> select * from table2; +----+---------+ | id | words | +----+---------+ | 1 | yellow | | 2 | blue | | 3 | cookies | | 4 | milk | +----+---------+ 4 rows in set (0.00 sec) 

The results of my query

 mysql> SELECT -> table1.id, -> table2_A.words colorname, -> table2_B.words foodname -> FROM table1 -> LEFT JOIN table2 table2_A ON table1.color=table2_A.id -> LEFT JOIN table2 table2_B ON table1.food=table2_B.id -> ; +----+-----------+----------+ | id | colorname | foodname | +----+-----------+----------+ | 1 | yellow | cookies | | 2 | yellow | milk | | 3 | yellow | cookies | | 4 | yellow | milk | | 5 | blue | cookies | | 6 | blue | milk | | 7 | blue | cookies | | 8 | blue | milk | +----+-----------+----------+ 8 rows in set (0.00 sec) mysql> 

UPDATE 2012-05-14 19:10 EDT

In case there are values ​​for food or color that aren't there, here is the adjusted query:

 SELECT table1.id, IFNULL(table2_A.words,'Unknown Color') colorname, IFNULL(table2_B.words,'Unknown Food') foodname FROM table1 LEFT JOIN table2 table2_A ON table1.color=table2_A.id LEFT JOIN table2 table2_B ON table1.food=table2_B.id; 

I will add rows to table1 and run this new query

 mysql> drop database if exists supercoolville; Query OK, 2 rows affected (0.13 sec) mysql> create database supercoolville; Query OK, 1 row affected (0.00 sec) mysql> use supercoolville; Database changed mysql> create table table1 -> ( -> id int not null auto_increment, -> color int, -> food int, -> primary key (id) -> ); Query OK, 0 rows affected (0.08 sec) mysql> insert into table1 (color,food) values -> (1,3),(1,4),(1,3),(1,4), -> (2,3),(2,4),(2,3),(2,4), -> (5,3),(5,4),(2,6),(2,8); Query OK, 12 rows affected (0.07 sec) Records: 12 Duplicates: 0 Warnings: 0 mysql> create table table2 -> ( -> id int not null auto_increment, -> words varchar(20), -> primary key (id) -> ); Query OK, 0 rows affected (0.08 sec) mysql> insert into table2 (words) values -> ('yellow'),('blue'),('cookies'),('milk'); Query OK, 4 rows affected (0.06 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> select * from table1; +----+-------+------+ | id | color | food | +----+-------+------+ | 1 | 1 | 3 | | 2 | 1 | 4 | | 3 | 1 | 3 | | 4 | 1 | 4 | | 5 | 2 | 3 | | 6 | 2 | 4 | | 7 | 2 | 3 | | 8 | 2 | 4 | | 9 | 5 | 3 | | 10 | 5 | 4 | | 11 | 2 | 6 | | 12 | 2 | 8 | +----+-------+------+ 12 rows in set (0.00 sec) mysql> select * from table2; +----+---------+ | id | words | +----+---------+ | 1 | yellow | | 2 | blue | | 3 | cookies | | 4 | milk | +----+---------+ 4 rows in set (0.00 sec) mysql> SELECT -> table1.id, -> IFNULL(table2_A.words,'Unknown Color') colorname, -> IFNULL(table2_B.words,'Unknown Food') foodname -> FROM table1 -> LEFT JOIN table2 table2_A ON table1.color=table2_A.id -> LEFT JOIN table2 table2_B ON table1.food=table2_B.id; +----+---------------+--------------+ | id | colorname | foodname | +----+---------------+--------------+ | 1 | yellow | cookies | | 2 | yellow | milk | | 3 | yellow | cookies | | 4 | yellow | milk | | 5 | blue | cookies | | 6 | blue | milk | | 7 | blue | cookies | | 8 | blue | milk | | 9 | Unknown Color | cookies | | 10 | Unknown Color | milk | | 11 | blue | Unknown Food | | 12 | blue | Unknown Food | +----+---------------+--------------+ 12 rows in set (0.00 sec) mysql> 

Given any invalid data, a LEFT JOIN is still necessary.

Answer 2

to try:

 SELECT table1.id, colorcodes.words, foodcodes.words FROM table1 LEFT JOIN table2 as colorcodes ON colorcodes.id = table1.color LEFT JOIN table2 as foodcodes ON foodcodes.id= table1.food 

Answer 3

Here is the request:

 SELECT a.id as id, b.words as colorname, c.words as foodname FROM table1 a LEFT JOIN table2 b ON b.id = a.color LEFT JOIN table2 c ON c.id = a.food 

Note. From your data it is clear that LEFT JOIN not required. If there are no rows in table 1 where either color or food is zero, then you can leave LEFT .