mysql: duplicate column name on join with subquery

Question

I've searched a lot but I still don't get it.

Here's my sample code

SELECT sp.testno, sp.companyid, st.* 
FROM sponsor AS sp
LEFT JOIN
(
    SELECT a.sponsorempno, (CASE WHEN t.companyid IS NULL OR t.companyid = '' THEN'aa' ELSE t.companyid END) agncy, a.controlno, a.tnpl, t.* 
    FROM applicant AS a
    LEFT JOIN
        test AS t
    ON a.controlno = t.controlno 
) AS st
ON sp.testno = st.testno

I still returns an error:

#1060 - Duplicate column name 'controlno'

Can somebody tell me what's wrong with the code?

The code (=question) lacks sample data, and DDL for the tables involved. — Luuk
– Luuk, Commented Nov 18, 2020 at 7:23
You get two columns with the same name from different tables - a.controlno explicitly and t.controlno implicitly from t.*. Assign an alias to at least one of columns (for example, ... , a.controlno AS a_controlno, ...). Or replace ON a.controlno = t.controlno with USING (controlno) - these columns will be collapsed into one with the value taken from left table (a.controlno). — Akina
– Akina, Commented Nov 18, 2020 at 7:25
Already answered here: stackoverflow.com/questions/9260936/… — Luuk
– Luuk, Commented Nov 18, 2020 at 7:26

Dominik Klug · Accepted Answer · 2020-11-18 07:59:48Z

1

In the subselect of your join, you are selecting a.controlno and by t.* t.controlno. You should provide an alias for one of the selected columns. In your case a.controlno. This is necessary, because the table aliases of the inner select are lost, when accessing it from the outer one.

The statement below should work, if there aren't any other duplicate column names in test and the set of used columns from applicant.

SELECT sp.testno, sp.companyid, st.* 
FROM sponsor AS sp
LEFT JOIN
(
    SELECT a.sponsorempno, (CASE WHENt.companyid IS NULL OR t.companyid = '' THEN'aa' ELSE t.companyid END) agncy, a.controlno as a_controlno, a.tnpl, t.* 
    FROM applicant AS a
    LEFT JOIN
        test AS t
    ON a.controlno = t.controlno 
) AS st
ON sp.testno = st.testno

edited Nov 18, 2020 at 7:59

answered Nov 18, 2020 at 7:21

Dominik Klug

1691 silver badge5 bronze badges

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

1 Comment

Jorz Over a year ago

it worked! thanks for this! now I understand better.

Collectives™ on Stack Overflow

mysql: duplicate column name on join with subquery

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related