query error duplicate column id sql

Question

Why this query return with error of duplicate id? I'm using php

SELECT DISTINCT * FROM products  as prd
    LEFT OUTER JOIN  (SELECT DISTINCT * FROM product_aliases) AS product_aliases
    ON product_aliases.product_id = prd.id 
    AND product_aliases.alias = '$alias'

    LEFT OUTER JOIN  (SELECT DISTINCT * FROM product_images as prdim
        LEFT OUTER JOIN  product_image_votes as prdimvt 
            ON prdimvt.product_image_id = prdim.id) AS productimages 
    ON productimages.product_id = prd.id 
WHERE prd.id = $id

ERROR:

Database Error Error: SQLSTATE[42S21]: Column already exists: 1060 Duplicate column name 'id' SQL Query: SELECT DISTINCT * FROM products as prd LEFT OUTER JOIN (SELECT DISTINCT * FROM product_aliases) AS product_aliases ON product_aliases.product_id = prd.id AND product_aliases.alias = 'Pringles The Original' LEFT OUTER JOIN (SELECT DISTINCT * FROM product_images as prdim LEFT OUTER JOIN product_image_votes as prdimvt ON prdimvt.product_image_id = prdim.id) AS productimages ON productimages.product_id = prd.id WHERE prd.id = 1

I suspect this has to do with the sub select join. I think this query can be written without the sub selects. — datasage
– datasage, Commented Jan 27, 2013 at 21:35
I need sub query because I have to put limit after and because I want to use distinct in the select inside @datasage — Alessandro Minoccheri
– Alessandro Minoccheri, Commented Jan 27, 2013 at 21:36
Distinct would only reduce duplicates, are there actually duplicates in those tables? — datasage
– datasage, Commented Jan 27, 2013 at 21:38

codingbiz · Accepted Answer · 2013-01-27 21:37:55Z

2

You have 3 tables in your query and it is possible at least 2 of them have the same column id. Using * in your select would select all columns in these three tables selecting id multiple times

You could solve this by specifying the columns using the alias

SELECT DISTINCT prd.id, prd.Name FROM products  as prd
LEFT OUTER JOIN  (SELECT DISTINCT * FROM product_aliases) AS product_aliases
ON product_aliases.product_id = prd.id 
AND product_aliases.alias = '$alias'
... rest of your query here

You could also do

SELECT DISTINCT prd.* FROM products  as prd

if you don't want to list them one by one

answered Jan 27, 2013 at 21:37

codingbiz

26.4k9 gold badges62 silver badges99 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Alessandro Minoccheri Over a year ago

In every select I have to put name_table.* and now no return error. Thanks

Collectives™ on Stack Overflow

query error duplicate column id sql

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related