join? union? data - 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)

join? union? data

Author

Topic

sphericalx
Starting Member

10 Posts

Posted - 2008-12-15 : 23:55:43

catA
catdescription
wax
tanning
hairextension
facial

catB
catdescription
facial
wax
hair treatment
================
Output
catdescription
wax
tanning
hairextension
facial
hair treatment

i have 2 tables. however there are some values in both tables which have the same data.

how can i do a query to retrieve out all the catdescription and all those of the same data will onli show once.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-12-15 : 23:58:48

Use Union.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-15 : 23:59:46

[code]
SELECT catdescription
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY catdescription ORDER BY cat) AS Seq,
catdescription
FROM
(
SELECT catdescription,1 as cat
FROM catA
UNION ALL
SELECT catdescription,2
FROM catB
)t
)r
WHERE seq=1
[/code]

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2008-12-16 : 00:05:01

DECLARE @catA table( name varchar(32))
insert into @cata select 'catdescription' union all
select 'wax' union all select 'tanning' union all
select 'hairextension' union all select 'facial'

DECLARE @catb table( name varchar(32))
insert into @catb select 'catdescription' union all
select 'facial' union all select 'wax' union all
select 'hair treatment'

select name from @cata union select name from @catb

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-16 : 00:08:44

quote:
Originally posted by bklr

DECLARE @catA table( name varchar(32))
insert into @cata select 'catdescription' union all
select 'wax' union all select 'tanning' union all
select 'hairextension' union all select 'facial'

DECLARE @catb table( name varchar(32))
insert into @catb select 'catdescription' union all
select 'facial' union all select 'wax' union all
select 'hair treatment'

select name from @cata union select name from @catb

works well only thing is that order cannot always be guaranteed

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2008-12-16 : 00:33:04

quote:
Originally posted by visakh16

quote:
Originally posted by bklr

DECLARE @catA table( name varchar(32))
insert into @cata select 'catdescription' union all
select 'wax' union all select 'tanning' union all
select 'hairextension' union all select 'facial'

DECLARE @catb table( name varchar(32))
insert into @catb select 'catdescription' union all
select 'facial' union all select 'wax' union all
select 'hair treatment'

select name from @cata union select name from @catb

works well only thing is that order cannot always be guaranteed

k thanks for ur suggestion

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2008-12-16 : 02:38:56

Order can be guaranteed if you use an order by....
And consider using UNION ALL if it makes sense to do so - you will avoid the step of removing duplicates.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-16 : 02:43:41

quote:
Originally posted by LoztInSpace

Order can be guaranteed if you use an order by....
And consider using UNION ALL if it makes sense to do so - you will avoid the step of removing duplicates.

yup..thats why i used union all rather than union.

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2008-12-16 : 04:16:15

I meant this one:

select name from @cata union select name from @catb

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources