Duplicate column name - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Duplicate column name

Author

Topic

monkeyvu
Starting Member

9 Posts

Posted - 2007-10-19 : 00:28:40

Hi guys,

I'm having a small problem

. I have a query like following

select A from

(select * from ...) as Temp

And I have an error : Duplicate column name A

(select * from ...) <--- about 200 columns, so I can't specify every one...

Thanks for your help :)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-10-19 : 00:52:44

Could you post the actual query that you are running?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

monkeyvu
Starting Member

9 Posts

Posted - 2007-10-19 : 01:09:16

select Temp.r_id as group1, concat(concat(Temp.rs_dtOpen, ' --> '), ifNull(Temp.rs_dtClose, now())) as group2

from (select *

from tran_lines l join tran_hdrs h
on h.h_id_pk = l.tr_id
join tran_payments p
on h.h_id_pk = p.h_id
join register_ssn s
on h.r_id = s.r_id and h.h_dtlogged between s.rs_dtOpen and ifNull(rs_dtClose, now())

) as Temp

group by group1, group2

I have an error : Duplicate column name r_id

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-10-19 : 01:26:47

This does not look like T-SQL. What dbms are you using?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-19 : 01:28:39

When you are joining tables and you have same column name in both tables, you should put an alias after one of the column names to have a unique distinct name in the resultset.

E 12°55'05.25"
N 56°04'39.16"

monkeyvu
Starting Member

9 Posts

Posted - 2007-10-19 : 01:29:22

MySQL. ???

monkeyvu
Starting Member

9 Posts

Posted - 2007-10-19 : 01:30:14

I know that Peso, but I have about 200 columns... How can I put an alias after them ? :(

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-19 : 01:33:59

One the few that are conflicting.

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-19 : 01:34:56

And I see no point in first select * and then select a

select A from

(select * from ...) as Temp

use

select {tablename or table alias}.a from .....

E 12°55'05.25"
N 56°04'39.16"

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-10-19 : 01:37:20

quote:
Originally posted by monkeyvu

MySQL. ???

You'd be better off posting in a MySql forum then as this site is for Microsoft SQL Server. dbforums.com has a MySql forum that you could try.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

monkeyvu
Starting Member

9 Posts

Posted - 2007-10-19 : 01:44:29

To tkizer: I think it is almost the same... We are just discussing a small query. That query can't run with MSSQL ?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-10-19 : 01:45:40

quote:
Originally posted by monkeyvu

That query can't run with MSSQL ?

Your query would not work on MSSQL. You are using functions in MySql that are not available in MSSQL.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-19 : 01:46:56

It can't run on ANY DBMS.
Do you want the query engine to be psychic? Since there are at least two columns named A, how do you expect query engine to pick one at random?
You have to specify which of the columns named A to pick.

E 12°55'05.25"
N 56°04'39.16"

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-10-19 : 01:47:06

And to resolve your problem, you'll need to remove * and list out your columns so that you can alias the columns that exist in more than one table from your joins. Do you really want all 200 columns returned though?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

monkeyvu
Starting Member

9 Posts

Posted - 2007-10-19 : 01:53:06

To Peso: You have to specify which of the columns named A to pick. <--- Any of them is ok :) Can we return only one "A" column ?

To tkizer: Your query would not work on MSSQL. You are using functions in MySql that are not available in MSSQL. <--- That is not the main point in my issue :) I really want to return 200 columns. Thanks for your help anyway :)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-10-19 : 01:54:15

We've both already answered your question though. You can't use select * since you've got same column names in your joined tables.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

monkeyvu
Starting Member

9 Posts

Posted - 2007-10-19 : 01:59:15

Ok :) I just think that we have a function which can automatically restrict the column when we have many same columns in query or we can just select the first one of them. Thanks for your help again :)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-10-19 : 02:00:54

It's not magic. You have to tell the dbms exactly what you intend.

But perhaps MySql can do it, which is why you should post your MySql questions in a MySql forum.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Kristen
Test

22859 Posts

Posted - 2007-10-19 : 02:10:36

And to be realistic we could give you an MSSQL solution which then might not work in MySQL ...

If you don't get any answers from the MySQL crowd come back and tell us so, and ask again, but I really think they will be a better resource for you initially.

They probably won't be such Jolly Good Chaps as us ... but Hey! you can't have everything

Kristen

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-10-19 : 04:54:23

quote:
Originally posted by monkeyvu

select Temp.r_id as group1, concat(concat(Temp.rs_dtOpen, ' --> '), ifNull(Temp.rs_dtClose, now())) as group2

from (select *

from tran_lines l join tran_hdrs h
on h.h_id_pk = l.tr_id
join tran_payments p
on h.h_id_pk = p.h_id
join register_ssn s
on h.r_id = s.r_id and h.h_dtlogged between s.rs_dtOpen and ifNull(rs_dtClose, now())

) as Temp

group by group1, group2

I have an error : Duplicate column name r_id

Dont use * which includes same columns from more than one table
Specify each column. If there are lot of columns then run this and make use of the result

select concat(column_name,',') from information_schema.columns
where table_name='your table'

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources