sql - MySQL:删除 Left Join 上的重复列，3 个表

Question

我有一个表将 3 个外键用于其他表。当我执行左连接时，我得到了重复的列。 MySQL 表示 USING 语法将减少重复列，但没有多个键的示例。

给定:

mysql> describe recipes;
+------------------+------------------+------+-----+---------+-------+
| Field            | Type             | Null | Key | Default | Extra |
+------------------+------------------+------+-----+---------+-------+
| ID_Recipe        | int(11)          | NO   | PRI | NULL    |       |
| Recipe_Title     | char(64)         | NO   |     | NULL    |       |
| Difficulty       | int(10) unsigned | NO   |     | NULL    |       |
| Elegance         | int(10) unsigned | NO   |     | NULL    |       |
| Quality          | int(10) unsigned | NO   |     | NULL    |       |
| Kitchen_Hours    | int(10) unsigned | NO   |     | NULL    |       |
| Kitchen_Minutes  | int(10) unsigned | NO   |     | NULL    |       |
| Total_Hours      | int(10) unsigned | NO   |     | NULL    |       |
| Total_Minutes    | int(10) unsigned | NO   |     | NULL    |       |
| Serving_Quantity | int(10) unsigned | NO   |     | NULL    |       |
| Description      | varchar(128)     | NO   |     | NULL    |       |
| ID_Prep_Text     | int(11)          | YES  |     | NULL    |       |
| ID_Picture       | int(11)          | YES  |     | NULL    |       |
| Category         | int(10) unsigned | NO   |     | NULL    |       |
| ID_Reference     | int(11)          | YES  |     | NULL    |       |
+------------------+------------------+------+-----+---------+-------+
15 rows in set (0.06 sec)

mysql> describe recipe_prep_texts;
+------------------+---------------+------+-----+---------+-------+
| Field            | Type          | Null | Key | Default | Extra |
+------------------+---------------+------+-----+---------+-------+
| ID_Prep_Text     | int(11)       | NO   | PRI | NULL    |       |
| Preparation_Text | varchar(2048) | NO   |     | NULL    |       |
+------------------+---------------+------+-----+---------+-------+
2 rows in set (0.02 sec)

mysql> describe recipe_prep_texts;
+------------------+---------------+------+-----+---------+-------+
| Field            | Type          | Null | Key | Default | Extra |
+------------------+---------------+------+-----+---------+-------+
| ID_Prep_Text     | int(11)       | NO   | PRI | NULL    |       |
| Preparation_Text | varchar(2048) | NO   |     | NULL    |       |
+------------------+---------------+------+-----+---------+-------+
2 rows in set (0.02 sec)

mysql> describe mp_references;
+--------------+---------+------+-----+---------+-------+
| Field        | Type    | Null | Key | Default | Extra |
+--------------+---------+------+-----+---------+-------+
| ID_Reference | int(11) | NO   | PRI | NULL    |       |
| ID_Title     | int(11) | YES  |     | NULL    |       |
| ID_Category  | int(11) | YES  |     | NULL    |       |
+--------------+---------+------+-----+---------+-------+
3 rows in set (0.00 sec)

我的查询语句:

SELECT  *
 FROM  Recipes
LEFT JOIN (Recipe_Prep_Texts, Recipe_Pictures, mp_References)
ON (
 Recipe_Prep_Texts.ID_Prep_Text = Recipes.ID_Prep_Text AND
 Recipe_Pictures.ID_Picture = Recipes.ID_Picture AND
 mp_References.ID_Reference = Recipes.ID_Reference
);

我的目标是从连接中获取所有列中的一行，而没有重复的列。我正在使用 MySQL C++ 连接器发送 SQL 语句并检索结果集。我认为 C++ 连接器存在重复列名的问题。

那么我应该使用的 SQL 语句语法是什么？

Reference to MySQL JOIN syntax

Answer 1

我相信以下应该可行:

SELECT  *
 FROM  Recipes
LEFT JOIN Recipe_Prep_Texts USING (ID_Prep_Text)
LEFT JOIN Recipe_Pictures USING (ID_Picture)
LEFT JOIN mp_References USING (ID_Reference)