I’ve got following tables:
Supplier |
---|
idSupplier |
Name |
Product |
---|
idProduct |
idSupplier |
EAN |
Now I want to select all values from both tables. I’m using this Query:
SELECT
Supplier.*,
Product.*
FROM
Supplier
INNER JOIN
Product
ON Supplier.idSupplier = Product.idSupplier
The issue is, that MySQL/MariaDB complains about a duplicate column name. I know, that you can eliminate the issue by listing all field separately and define a alias for the field Product.idSupplier
or just by excluding it.
But since I’m lazy and will probably forget, that this has to be expanded if I ever update or expand a table, I want a ‘automatic’ solution. This means, every newly added field should automatically be included in the result.
There are probably some MySQL magicians who can solve this.
2
Answers
It’s always better to explicitly specify the columns you want.
But in this particular case, you can join using
USING
instead ofON
and doing aSELECT *
instead oftablename.*
to only get one copy of the column:MySQL doesn’t complain about duplicate column names unless you wrap the query you show as a subquery.
If you can’t give up your habit of using
SELECT *
, then you can solve it by making sure to define distinct column names in each table.Boom! Problem solved. 🖐️🎤
But there’s a problem in your original conditions:
Any solution we suggest can be circumvented by at least one of these.