Select same column w different names in 2 tables

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

General SQL Server Forums

New to SQL Server Programming

Select same column w different names in 2 tables

Author

Topic

rocco2008
Yak Posting Veteran

63 Posts

Posted - 2008-11-14 : 06:43:48

Hi,

I am trying to select the same column with different names from two tables using "union". how do I make this work?? my code looks like below and I have no update/writning rights:

select
right('00'+convert(varchar(2),datepart(hh,convert(datetime,COMMON_COLUMN))),2)
, Size

FROM (
select
right('00'+convert(varchar(2),datepart(hh,convert(datetime,COMMON_COLUMN_NAME_1))),2)
, Size

FROM Table_1

WHERE Type IN ('A','B')

UNION
select
right('00'+convert(varchar(2),datepart(hh,convert(datetime,COMMON_COLUMN_NAME_2))),2)
, Size

FROM BookBeforeAuction

WHERE Type IN ('A','B')

) AS t

ORDER BY COMMON_COLUMN

Many tnx in advance

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-14 : 06:53:17

seeme like what you want is this

select t1.Col,t1.Size as t1Size,t2.Size as t2Size
from
(
select
right('00'+convert(varchar(2),datepart(hh,convert(datetime,COMMON_COLUMN_NAME_1))),2) AS Col
, Size 

FROM Table_1

WHERE Type IN ('A','B')

)t1
INNER JOIN
(
select
right('00'+convert(varchar(2),datepart(hh,convert(datetime,COMMON_COLUMN_NAME_2))),2) AS Col
, Size

FROM BookBeforeAuction

WHERE Type IN ('A','B')
)t2
ON t1.Col,t2.Col

rocco2008
Yak Posting Veteran

63 Posts

Posted - 2008-11-14 : 07:26:17

hey and thanks, I am not sure that a JOIN is the right way to go here.. Table 1 gives me the daily needed Information until 1pm and Table 2 the daily needed information after 1pm, thats why I was using UNION.. you're the expert, what do you say..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-14 : 07:30:02

then use this

select
right('00'+convert(varchar(2),datepart(hh,convert(datetime,COMMON_COLUMN))),2)
, t1Size,t2Size 

FROM ( 
select
right('00'+convert(varchar(2),datepart(hh,convert(datetime,COMMON_COLUMN_NAME_1))),2)
, Size  AS t1Size,CAST(NULL AS datatype) AS t2Size

FROM Table_1

WHERE Type IN ('A','B')

UNION
select
right('00'+convert(varchar(2),datepart(hh,convert(datetime,COMMON_COLUMN_NAME_2))),2)
NULL, Size

FROM BookBeforeAuction

WHERE Type IN ('A','B')


) AS t

ORDER BY COMMON_COLUMN

make sure you use correct datatype of size in first staement

rocco2008
Yak Posting Veteran

63 Posts

Posted - 2008-11-14 : 07:38:03

one more thing, I think you're confusing which is the column I am talking about.. 'size' is named identical in both tables, the problem is the COMMON_COLUMN in the 'convert'-part. It stores the same information, but is called COMMON_COLUMN_NAME_1 in table 1 and COMMON_COLUMN_NAME_2 in table 2...

THANK YOU SO MUCH!

rocco2008
Yak Posting Veteran

63 Posts

Posted - 2008-11-18 : 07:38:51

Nobody any idea?? don't worry about the size-variable, I am just concerend about the time-column which has the different names (Commonname1 and Commonname 2). I tried this, but it doesn't work:

declare @common as varchar(20)

select
right('00'+convert(varchar(2),datepart(hh,convert(datetime,@common))),2)

FROM (
set @common = commonname1
select
right('00'+convert(varchar(2),datepart(hh,convert(datetime,@common))),2)

FROM Table_1

WHERE Type IN ('A','B')

UNION
@common = commonname2
select
right('00'+convert(varchar(2),datepart(hh,convert(datetime,@common))),2)

FROM BookBeforeAuction

WHERE Type IN ('A','B')

) AS t

ORDER BY @common

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2008-11-18 : 08:06:06

quote:
Originally posted by rocco2008

one more thing, I think you're confusing which is the column I am talking about.. 'size' is named identical in both tables, the problem is the COMMON_COLUMN in the 'convert'-part. It stores the same information, but is called COMMON_COLUMN_NAME_1 in table 1 and COMMON_COLUMN_NAME_2 in table 2...

What difference does that make?
None. That's what.
In a UNION query, the column names for the result set are determined by the first clause. There is no requirement that the underlying columns have the same name.

If it is not practically useful, then it is practically useless.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-18 : 08:49:36

quote:
Originally posted by rocco2008

Nobody any idea?? don't worry about the size-variable, I am just concerend about the time-column which has the different names (Commonname1 and Commonname 2). I tried this, but it doesn't work:

declare @common as varchar(20)

select
right('00'+convert(varchar(2),datepart(hh,convert(datetime,@common))),2)

FROM (
set @common = commonname1
select
right('00'+convert(varchar(2),datepart(hh,convert(datetime,@common))),2)

FROM Table_1

WHERE Type IN ('A','B')

UNION
@common = commonname2
select
right('00'+convert(varchar(2),datepart(hh,convert(datetime,@common))),2)

FROM BookBeforeAuction

WHERE Type IN ('A','B')

) AS t

ORDER BY @common

you cant set a variable like this inside derived table. whats the purpose of using it? wont below be sufficient


select
right('00'+convert(varchar(2),datepart(hh,convert(datetime,common))),2)
 

FROM ( 

select 
right('00'+convert(varchar(2),datepart(hh,convert(datetime,commonname1))),2) as common 


FROM Table_1

WHERE Type IN ('A','B')

UNION
select
right('00'+convert(varchar(2),datepart(hh,convert(datetime,commonname2))),2)


FROM BookBeforeAuction

WHERE Type IN ('A','B')


) AS t

ORDER BY common

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources