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 语句语法呢？

参考 MySQL JOIN 语法

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)

Answer 2

因为看起来您要加入的大多数表除了第一列之外都有几列，怎么样：

   SELECT Recipes.*,
          Recipe_Prep_Texts.Preparation_Text,
          Recipe_Pictures.Foo, -- describe is missing in OP
          mp_References.ID_Title,
          mp_References.ID_Category

     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
          );

我无法告诉您我希望有多少次，

SELECT (* - foo) FROM table

特别是在 foo 是像 BLOB 这样的巨大字段的情况下我只想在不破坏格式的情况下查看其他所有内容。

Answer 3

您正在从合并的结果表中选择 *。将 * 限制为您想要保留的任何列。

Answer 4

尝试以下查询：

SELECT  name,ac,relation_name
FROM  table1
LEFT JOIN table2 USING (ID_Prep_Text)
LEFT JOIN table3 USING (ID_Picture);