UNION - 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 2005 Forums

Transact-SQL (2005)

UNION

Author

Topic

Mir
Starting Member

19 Posts

Posted - 2009-06-04 : 07:01:56

Hi Guys,

I have two table, ticket1 and ticket2 both have the same columns,
how to combine this two tables? cAN SOMEONE HELP? I TRY TO USE UNION BUT FAIL
THANKS..

TICKET1
BUS, KM1,KM2,DISTANCE
WEX101 0 1 10
WEX101 1 2 20
WEX101 2 3 30
WEX101 3 4 NULL
WEX101 4 5 NULL
WEX101 5 6 NULL
WEX101 6 7 70
WEX101 7 8 NULL
WEX101 8 9 NULL
WEX101 9 10 100

TICKET1
BUS, KM1,KM2,DISTANCE
WEX101 0 1 NULL
WEX101 1 2 NULL
WEX101 2 3 NULL
WEX101 3 4 401
WEX101 4 5 501
WEX101 5 6 601
WEX101 6 7 NULL
WEX101 7 8 801
WEX101 8 9 901
WEX101 9 10 NULL

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-06-04 : 07:25:20

What is the meaning of "combine this two tables"?
What is your expected output for given sample data?

No, you're never too old to Yak'n'Roll if you're too young to die.

Mir
Starting Member

19 Posts

Posted - 2009-06-04 : 07:55:49

quote:
Originally posted by webfred

What is the meaning of "combine this two tables"?
What is your expected output for given sample data?

No, you're never too old to Yak'n'Roll if you're too young to die.

Hi webfred,

I want to combine both table mean that if distance from table ticket1 is null then it will be replace with the not null value from table ticket2. it is the same if the distance value from table ticket2 is null, then it will use the value from table ticket 1..

thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-04 : 07:58:44

[code]
select t1.BUS, t1.KM1, t1.KM2, distance = coalesce(t1.Distance, t2.Distance)
from TICKET1 t1 full outer join TICKET2 t2
on t1.BUS = t2.BUS
and t1.KM1 = t2.KM1
and t1.KM2 = t2.KM2
[/code]

KH
[spoiler]Time is always against us[/spoiler]

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-06-04 : 08:09:34

[code]declare @Ticket1 table(
BUS varchar(255),
KM1 int,
KM2 int,
DISTANCE int)

declare @Ticket2 table(
BUS varchar(255),
KM1 int,
KM2 int,
DISTANCE int)

insert @Ticket1
select 'WEX101', 0, 1, 10 union all
select 'WEX101', 1, 2, 20 union all
select 'WEX101', 2, 3, 30 union all
select 'WEX101', 3, 4, NULL union all
select 'WEX101', 4, 5, NULL union all
select 'WEX101', 5, 6, NULL union all
select 'WEX101', 6, 7, 70 union all
select 'WEX101', 7, 8, NULL union all
select 'WEX101', 8, 9, NULL union all
select 'WEX101', 9, 10, 100

insert @Ticket2
select 'WEX101', 0, 1, NULL union all
select 'WEX101', 1, 2, NULL union all
select 'WEX101', 2, 3, NULL union all
select 'WEX101', 3, 4, 401 union all
select 'WEX101', 4, 5, 501 union all
select 'WEX101', 5, 6, 601 union all
select 'WEX101', 6, 7, NULL union all
select 'WEX101', 7, 8, 801 union all
select 'WEX101', 8, 9, 901 union all
select 'WEX101', 9, 10, NULL

--select * from @Ticket1
--select * from @Ticket2

select
t1.BUS,
t1.KM1,
t1.KM2,
ISNULL(t1.DISTANCE,t2.DISTANCE) as DISTANCE
from @Ticket1 t1
join @Ticket2 t2
on t1.BUS = t2.BUS and
t1.KM1 = t2.KM1 and
t1.KM2 = t2.KM2
[/code]

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-06-04 : 08:10:25

Aarrgh again - toooooooooo late...

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-06-04 : 08:16:39

And I see KH.
FULL OUTER JOIN is the correct way for this case

Greetings
Webfred

No, you're never too old to Yak'n'Roll if you're too young to die.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-06-04 : 08:30:40

or

select bus,km1,km2,max(DISTANCE) as DISTANCE from
(
select bus,km1,km2,DISTANCE from @Ticket1
union all
select bus,km1,km2,DISTANCE from @Ticket2
) as t
group by bus,km1,km2

Madhivanan

Failing to plan is Planning to fail

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-06-04 : 08:49:57

There's only one way to rock (Sammy Hagar)
but there are always many ways to query

Webfred

No, you're never too old to Yak'n'Roll if you're too young to die.

Mir
Starting Member

19 Posts

Posted - 2009-06-07 : 07:22:57

quote:
Originally posted by webfred

There's only one way to rock (Sammy Hagar)
but there are always many ways to query

Webfred

No, you're never too old to Yak'n'Roll if you're too young to die.

Thanks webfred,

What if the Ticket1 table have extra data like below
TICKET1
BUS, KM1,KM2,DISTANCE
WEX101 0 1 10
WEX101 1 2 20
WEX101 2 3 30
WEX101 3 4 NULL
WEX101 4 5 NULL
WEX101 5 6 NULL
WEX101 6 7 70
WEX101 7 8 NULL
WEX101 8 9 NULL
WEX101 9 10 100
WEX102 0 1 221
WEX102 1 2 151
WEX102 2 3 303

hOW to make sure wex102 data together in the result even though
there is no match in Ticket2 table for it?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-06-07 : 08:18:58

Yeah that's why I posted KH's FULL OUTER JOIN is the correct way.
See here:

declare @Ticket1 table(
BUS varchar(255),
KM1 int,
KM2 int,
DISTANCE int)

declare @Ticket2 table(
BUS varchar(255),
KM1 int,
KM2 int,
DISTANCE int)

insert @Ticket1
select 'WEX101', 0, 1, 10 union all
select 'WEX101', 1, 2, 20 union all
select 'WEX101', 2, 3, 30 union all
select 'WEX101', 3, 4, NULL union all
select 'WEX101', 4, 5, NULL union all
select 'WEX101', 5, 6, NULL union all
select 'WEX101', 6, 7, 70 union all
select 'WEX101', 7, 8, NULL union all
select 'WEX101', 8, 9, NULL union all
select 'WEX101', 9, 10, 100 union all
select 'WEX102', 0, 1, 221 union all
select 'WEX102', 1, 2, 151 union all
select 'WEX102', 2, 3, 303

insert @Ticket2
select 'WEX101', 0, 1, NULL union all
select 'WEX101', 1, 2, NULL union all
select 'WEX101', 2, 3, NULL union all
select 'WEX101', 3, 4, 401 union all
select 'WEX101', 4, 5, 501 union all
select 'WEX101', 5, 6, 601 union all
select 'WEX101', 6, 7, NULL union all
select 'WEX101', 7, 8, 801 union all
select 'WEX101', 8, 9, 901 union all
select 'WEX101', 9, 10, NULL

--select * from @Ticket1
--select * from @Ticket2

select
t1.BUS,
t1.KM1,
t1.KM2,
ISNULL(t1.DISTANCE,t2.DISTANCE) as DISTANCE
from @Ticket1 t1
full outer join @Ticket2 t2
  on t1.BUS = t2.BUS and
     t1.KM1 = t2.KM1 and
     t1.KM2 = t2.KM2

No, you're never too old to Yak'n'Roll if you're too young to die.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-07 : 08:26:53

if you have records in TICKET1 not in TICKET2 or vice versa, then use FULL OUTER JOIN. I missed out the coalesce on the BUS & KM.


SELECT 	BUS 	 = coalesce(t1.BUS, 	 t2.BUS), 
	KM1	 = coalesce(t1.KM1, 	 t2.KM1),
	KM2	 = coalesce(t1.KM2, 	 t2.KM2),
	distance = coalesce(t1.Distance, t2.Distance)
FROM   	TICKET1 t1 FULL OUTER JOIN TICKET2 t2 
ON     	t1.BUS = t2.BUS
AND    	t1.KM1 = t2.KM1
AND    	t1.KM2 = t2.KM2

KH
[spoiler]Time is always against us[/spoiler]

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-06-07 : 08:36:50

My thought was that there are always records in Ticket1 and only Distance can be NULL.
But you are right KH.
When using full outer join then it is the save way to look for NULL values on each side.

Greetings
Webfred

No, you're never too old to Yak'n'Roll if you're too young to die.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-07 : 08:51:24

If such cases does not exists for OP then a LEFT JOIN is sufficient.

But from what OP describe here sounds like required a FULL OUTER JOIN to me

quote:
Originally posted by Mir

I want to combine both table mean that if distance from table ticket1 is null then it will be replace with the not null value from table ticket2. it is the same if the distance value from table ticket2 is null, then it will use the value from table ticket 1..

thanks

Alternatively, can also use the solution posted by Madhivanan. Using UNION ALL to combine both table and then GROUP BY BUS, KM1, KM2.

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources