skip to Main Content

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


  1. It’s always better to explicitly specify the columns you want.

    But in this particular case, you can join using USING instead of ON and doing a SELECT * instead of tablename.* to only get one copy of the column:

    SELECT *
    FROM
        Supplier
            INNER JOIN
        Product
            USING(idSupplier)
    
    Login or Signup to reply.
  2. 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:

    I’m lazy and will probably forget

    Any solution we suggest can be circumvented by at least one of these.

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search