MySQL：删除左连接上的重复列，3 个表

MySQL: Removing duplicate columns on Left Join, 3 tables

提问by Thomas Matthews

I have a table that uses 3 foreign keys into other tables. When I perform a left join, I get duplicate columns. MySQL says that the USINGsyntax will reduce the duplicate columns, but there aren't examples for multiple keys.

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

Given:

鉴于：

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)

My query statement:

我的查询语句：

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

My objective is to get one row of all the columns from the join without duplicate columns. I'm using MySQL C++ Connector to send the SQL statements and retrieve result sets. I believe that the C++ Connector is having issues with duplicate column names.

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

So what is the SQL statement syntax that I should use?

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

Reference to MySQL JOIN syntax

参考 MySQL JOIN 语法

回答by Danny Shisler

I believe the following should work:

我相信以下应该有效：

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)

回答by Rob Van Dam

Since it looks like most of the tables you are joining on have a few columns except for the first one, how about:

由于看起来您要加入的大多数表除了第一列之外都有几列，那么如何：

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

I can't tell you how many times I wished I had

我无法告诉你我希望我有多少次

SELECT (* - foo) FROM table

especially in cases where foo is some huge field like a BLOB and I just want to see everything else without breaking the formatting.

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

回答by tgandrews

You are selecting * from the combined resulting table. Limit that * to whatever columns you want to keep.

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

回答by padma

Try the following query:

尝试以下查询：

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