MySQL: Removing duplicate columns on Left Join, 3 tables
Solution 1
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)
Solution 2
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.
Solution 3
You are selecting * from the combined resulting table. Limit that * to whatever columns you want to keep.
Solution 4
Try the following query:
SELECT name,ac,relation_name
FROM table1
LEFT JOIN table2 USING (ID_Prep_Text)
LEFT JOIN table3 USING (ID_Picture);
Thomas Matthews
I am a Senior Software and Firmware Engineer with over 30 years experience in C and C++.
Updated on March 04, 2020Comments
-
Thomas Matthews about 4 years
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
USING
syntax will reduce the duplicate columns, but there aren't examples for multiple keys.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.
So what is the SQL statement syntax that I should use?
-
Thomas Matthews over 14 yearsAs you look in the post data from MySQL, I have a lot of columns. This may be a pain if I have to list all the columns except the duplicates. Is there an SQL qualifier that will let me list all columns except those I specify?
-
Thomas Matthews over 14 yearsThis works, except when the table doesn't exist. So I put in checks for that. Also, this assumes that the tables have unique column names except for the foreign key fields. Luckily, that is what I have. Many thanks!
-
Thomas Matthews over 14 yearsAlthough enumerating each valid column is possible, I just think it is a lot of work for my program and also adds to the length of the SQL statement that gets sent to the database. See Danny's answer below.