MySQL删除Left Join，3个表上的重复列 [英] MySQL Removing duplicate columns on Left Join, 3 tables

问题描述

我有三个表，每个都有一个外键。

  mysql>描述家庭; 
 + --------------- + ------------- + ------ + ----- +  - ------- + ------- + 
 |字段|类型| Null | Key |默认|额外| 
 + --------------- + ------------- + ------ + ----- +  - ------- + ------- + 
 | HEAD_name | varchar（45）|否| PRI | | | 
 | Family_Size | int（11）| NO | | | | 
 |性别| char（1）| NO | | | | 
 | ID_Number | int（11）| NO | | | | 
 | DOB |日期| NO | | | | 
 | Supervisor_ID | int（11）| NO | MUL | | | 
 + --------------- + ------------- + ------ + ----- +  - ------- + ------- + 
集合中的6行（0.00秒）
 
 mysql>描述SUPERVISOR; 
 + ------------------- + --------------- + ------ +  - --- + --------- + ------- + 
 |字段|类型| Null | Key |默认|额外| 
 + ------------------- + --------------- + ------ +  - --- + --------- + ------- + 
 | Supervisor_ID | int（11）| NO | PRI | | | 
 | Supervisor_Name | varchar（45）| NO | | | | 
 | Supervisor_Number |十进制（10,0）| NO | | | | 
 | Center_ID | int（11）| NO | MUL | | | 
 + ------------------- + --------------- + ------ +  - --- + --------- + ------- + 
集合中的4行（0.00秒）
 
 mysql>描述CENTER; 
 + ----------- + ------------- + ------ + ----- + ------ --- + ------- + 
 |字段|类型|空| Key |默认|额外| 
 + ----------- + ------------- + ------ + ----- + ------ --- + ------- + 
 | Center_ID | int（11）| NO | PRI | | | 
 |位置| varchar（45）| NO | | | | 
 + ----------- + ------------- + ------ + ----- + ------ --- + ------- + 
集合中的2行（0.00秒）
  

我的查询语句：

  SELECT * from Family 
 JOIN SUPERVISOR on（Family.Supervisor_ID = SUPERVISOR.Supervisor_ID ）
 JOIN CENTER on（SUPERVISOR.Center_ID = CENTER.Center_ID）; 
  

我的目标是从连接中获取所有列的一行，而没有重复的列。
所以，什么是我应该使用的SQL语句语法？

解决方案

如果您使用 * 。您将需要在查询中显式输入列名称，以按需要检索它们。使用查询如下：

  SELECT A.HEAD_name，A.Family_Size，A.Gender，A.ID_Number，A.DOB ，
 B.Supervisor_ID，B.Supervisor_Name，B.Supervisor_Number，
 C.Center_ID，C.Location 
 FROM Family A 
加入SUPERVISOR B on（A.Supervisor_ID = B. Supervisor_ID）
 JOIN CENTER C on（B.Center_ID = C.Center_ID）; 
  

I have three tables, each have a foreign key. When I perform a join, I get duplicate columns.

Given

mysql> describe Family;
+---------------+-------------+------+-----+---------+-------+
| Field         | Type        | Null | Key | Default | Extra |
+---------------+-------------+------+-----+---------+-------+
| HEAD_name     | varchar(45) | NO   | PRI |         |       |
| Family_Size   | int(11)     | NO   |     |         |       |
| Gender        | char(1)     | NO   |     |         |       |
| ID_Number     | int(11)     | NO   |     |         |       |
| DOB           | date        | NO   |     |         |       |
| Supervisor_ID | int(11)     | NO   | MUL |         |       |
+---------------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

mysql> describe SUPERVISOR;
+-------------------+---------------+------+-----+---------+-------+
| Field             | Type          | Null | Key | Default | Extra |
+-------------------+---------------+------+-----+---------+-------+
| Supervisor_ID     | int(11)       | NO   | PRI |         |       |
| Supervisor_Name   | varchar(45)   | NO   |     |         |       |
| Supervisor_Number | decimal(10,0) | NO   |     |         |       |
| Center_ID         | int(11)       | NO   | MUL |         |       |
+-------------------+---------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> describe CENTER;
+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| Center_ID | int(11)     | NO   | PRI |         |       |
| Location  | varchar(45) | NO   |     |         |       |
+-----------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

My query statement:

SELECT * from Family
   JOIN SUPERVISOR on ( Family.Supervisor_ID = SUPERVISOR.Supervisor_ID)
   JOIN CENTER on (SUPERVISOR.Center_ID = CENTER.Center_ID); 

My objective is to get one row of all the columns from the join without duplicate columns. So what is the SQL statement syntax that I should use?

解决方案

By default MySQL will return all columns for all tables if you use *. You will need to explicitly enter column names in your query to retrieve them the way you want. Use the query as follows:

SELECT A.HEAD_name, A.Family_Size, A.Gender, A.ID_Number, A.DOB,
    B.Supervisor_ID, B.Supervisor_Name, B.Supervisor_Number,
    C.Center_ID, C.Location
FROM Family A
JOIN SUPERVISOR B on ( A.Supervisor_ID = B.Supervisor_ID)
JOIN CENTER C on (B.Center_ID = C.Center_ID);

这篇关于MySQL删除Left Join，3个表上的重复列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！