2

So I have three tables: tabe_1, table_2, table_3, I'm going to use a field A to map the first two tables, since it is included in both table_1 and table_2, then join with table_3 with field B and C, then add some filters (e.g: where statement) on top of that, the query is:

SELECT *
FROM 
(select *
from table_1 t1 
join table_2 t2 
on t1.A = t2.A
join table_3 t3
on t1.B  = t3.B 
and t1.C = t3.C) AS output_table

WHERE output_table.xx = xxx

This gave me error: Error Code: 1060. Duplicate column name 'A'

But if I only query the subquery:

select *
from table_1 t1 
join table_2 t2 
on t1.A = t2.A
join table_3 t3
on t1.B  = t3.B 
and t1.C = t3.C

This will return the output_table, can someone take a look what is going on with the nested query? Thanks.

Improve this question
3
  • Do you use select * in the subquery. List the columns in the subquery. Commented May 17, 2021 at 14:07
  • Yes please see the query above: FROM (select * from.......) AS output_table Commented May 17, 2021 at 14:09
  • List the column in the subquery rather than using select *. I think my earlier comment got all jumbled. Commented May 17, 2021 at 20:48

3 Answers 3

Reset to default
1

Because you SQL query need the ability to distinct subquery fields in order to treat it as a table type record source.

Here is an example of what's happen :

with table_1 as (select 0 A, 0 B, 0 C),
     table_2 as (select 0 A, 0 D),
     table_3 as (select 1 A, 0 B, 0 C)
SELECT *
FROM 
(select *
from table_1 t1 
join table_2 t2 
on t1.A = t2.A
join table_3 t3
on t1.B  = t3.B 
and t1.C = t3.C) AS output_table
WHERE output_table.D = 0;

This fails because the subquery has field t1.A/t1.B/t1.C and t2.A/t2.D and t3.A/t3.B/t3.C.

If you don't make it a subquery, then the MySQL engine doesn't need to distinct the fields and can output records with all fields indistinctively. From your case, the query that works :

with table_1 as (select 0 A, 0 B, 0 C),
     table_2 as (select 0 A, 0 D),
     table_3 as (select 1 A, 0 B, 0 C)
select *
from table_1 t1 
join table_2 t2 
on t1.A = t2.A
join table_3 t3
on t1.B  = t3.B 
and t1.C = t3.C;

So, to avoid the problem, select precisely what fields you requires from your subquery, like that :

with table_1 as (select 0 A, 0 B, 0 C),
     table_2 as (select 0 A, 0 D),
     table_3 as (select 1 A, 0 B, 0 C)
SELECT *
FROM 
(select t1.*, t2.D
from table_1 t1 
join table_2 t2 
on t1.A = t2.A
join table_3 t3
on t1.B  = t3.B 
and t1.C = t3.C) AS output_table
WHERE output_table.D = 0;

To be more clear, imagine that you want to join another table with your subquery ((subquery) AS output_table join another_table t4 on t4.A = output_table.A, how the MySQL engine can determine which field A from output_table it should use to join with another_table between t1.A (0) and T3.A (1) ? It can't, unless you specify only one field 'A' in your subquery.

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

Comments

1

In your subquery, you have column A & column A in t1 & t2, so there is ambiguity. Try alias name for the column, that should make things easy.

Improve this answer

Comments

0

Probably table_1 and table_2 have at least one column which exists in both tables (your column A). Thus when your subquery executes as select * from ... it returns two columns with the same name. This is not a problem if you just do this subquery alone, but you cannot query from the result of that subquery

Rewrite your subquery to

select t1.A, t1.B, t1.C, t2.whatever, t3.idontknow, ...
....

and make sure, to select the columns you are joining on (or that exists in mor than one table) only from one table.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.