[Solved] UNION SELECT - 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)

[Solved] UNION SELECT

Author

Topic

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2008-10-02 : 11:59:54

Hi,

I have made a View which unions 2 tables which have some overlapping ID fields. I would like to display another column which flags if row came from one the other or both tables. I can use a CASE to insert eg 'A' or 'B' but this makes duplicate rows. I would like these tags concatenated.

Eg
select x, TagColumn from A
Union
select x, TagColumn from B

with result of:

x TagColumn
1 , AB
2 , A
3 , B
4 , AB
5 , A

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-02 : 12:25:03

select x, case when x in (select a.x from a, b where a.x = b.x) then 'AB' else 'A' end
from a
UNION
select x, case when x in (select b.x from a, b where a.x = b.x) then 'AB' else 'B' end
from b

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-02 : 13:51:09

[code]SELECT x,
CASE WHEN MAX(Cat)=MIN(Cat) THEN MAX(Cat) ELSE 'AB' END
FROM
(
select x,'A' AS Cat from A
Union All
select x, 'B' from B
)t
GROUP BY x[/code]

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2008-10-03 : 05:06:30

Thanks for your help. I had to modify your code to not show a tag column value where there was a null. I've added my modified code in case its ever needed by some-one else. Thanks again.

select [ID], x, case when [ID] in
(select a.[ID] from a, b where a.x = b.x and a.x is not null and b.x is not null)
then 'AB' else 'A' end
from a
where a.x is not null
UNION
select [ID], x, case when [ID] in
(select b.[ID] from a, b where a.x = b.x and a.x is not null and b.x is not null)
then 'AB' else 'B' end
from b
where b.x is not null

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-03 : 05:23:27

Did you try solution i suggested?

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-03 : 10:35:44

visakh16's suggestion will work too, it is also more efficient.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources