更新时间:2022-11-30 16:39:19
如果您使用 *
。您将需要在查询中显式输入列名称,以按需要检索它们。使用查询如下:
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);