LEFT JOIN AS new column?

database, join, mysql

Solution

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;

Your Sample Data

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)

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 the event there are values for food or color that do not exist, 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, `LEFT JOIN` in still needed.

Problem

Original source