MySQL Removing duplicate columns on Left Join, 3 tables
Solution 1
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);
Solution 2
The problem can be solved by "USING" keyword.
SELECT * from Family
JOIN SUPERVISOR on ( Family.Supervisor_ID = SUPERVISOR.Supervisor_ID)
JOIN CENTER on (SUPERVISOR.Center_ID = CENTER.Center_ID);
In your case the query will become
SELECT * FROM FAMILY
JOIN (SUPERVISOR JOIN CENTER USING(Center_ID)) USING(Supervisor_ID);
The point is Simple, If you have two Tables A(a,b) and B(b,c) then after joining to produce the result in the form of (a,b,c)
Select *
from A JOIN B USING(b);
will give the Result-Set with three columns(a,b,c)
NOTE : Since I don't know whether we can use multiple params in Using, therefore I made it as subquery.
Solution 3
You are not getting duplicate columns, what you are really getting is the Supervisor_ID column from table Family (that is Family.Supervisor_ID) and Supervisor_ID from table Supervisor (that is Supervisor.Supervisor_ID) but to your result set, you will see both as Supervisor_ID, and that is why you think they are duplicated. The same will happen with Center_iD.
The solution is to specify the fields that you need from each table, and decide if you need to get the Supervisor_ID and Center_ID and which table to get it from.
Mohammed Ahmed
Updated on May 12, 2020Comments
-
Mohammed Ahmed about 4 years
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?
-
paddy over 11 yearsIf you are feeling lazy, you can take all columns from the largest table (in this case
A
) and then add in any remaining ones you want from the others:A.*, B.Supervisor_Name, B.Supervisor_Number, B.*
. -
Tanzeel Kazi over 11 years@paddy Sure that works! But if you want to optimize your query never use
*
. It adds an extra lookup for the table column names. In a small query it might not matter but it can add up for multiple queries in the long run. -
paddy over 11 yearsCool, that's good to know. I never do, unless I'm just doing throw-away queries.