LEFT JOIN ALS neue Spalte?

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;

Beispieldaten

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)

Ergebnisse Meiner Abfrage

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 der event es sind die Werte, die für Lebensmittel oder Farbe, die nicht existieren, hier ist die angepasste Abfrage:

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;

Will ich hinzufügen, Zeilen auf Tabelle1 und führen diese neue Abfrage

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>

Irgendwelche ungültigen Daten LEFT JOIN im noch benötigt.

Hier ist die Abfrage:

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

Hinweis: Es sieht aus Ihren Daten, dass eine LEFT JOIN ist nicht erforderlich. Wenn es keine Zeilen in "Tabelle1", in denen entweder die Farbe oder Lebensmittel, die null sind, dann können Sie aufhören, die LEFT.