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 2000 Forums

SQL Server Development (2000)

UNION

Author

Topic

dhenderson01
Starting Member

4 Posts

Posted - 2006-05-27 : 18:05:00

I have 2 tables for which I'm using the UNION command. I want to pull recs from both, but don't want duplicates.
Here's my question:
Is there a way to determine if the record doesnt't exist on one of the tables? Say, there is no match on TableA, but there is a match on TableB. Could I somehow create an extra field in TableA and populate it with say, "on" when it finds the match or maybe use the EXISTS command?
Any suggestions?

select field1 from TableA where field1 = ?
union
select field1 from TableB where field1 = ?;

Don

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-05-27 : 23:04:30

Is this what you want ?

select field1, case when exists(select * from TableB x where x.field1 = a.field1) then 'on' else null end
       from TableA a
or

select	a.field1, case when b.field1 is not null then 'on' else null end
from	TableA a left join TableB b
	on	a.field1	= b.field1

dhenderson01
Starting Member

4 Posts

Posted - 2006-05-28 : 00:31:42

Thanks Khtan,
I used the 1st example and it works fine!

Thanks so very much!

Don

PSamsig
Constraint Violating Yak Guru

384 Posts

Posted - 2006-05-28 : 11:54:56

Here is an alternative approach:

SELECT field1, MAX(is_in_A), MAX(is_in_B)
FROM (
   SELECT field1, 1 AS is_in_A, 0 AS is_in_B
   FROM TableA
   UNION
   SELECT field1, 0 AS is_in_A, 1 AS is_in_B
   FROM TableB) DTbl
GROUP BY field1

-- This one's tricky. You have to use calculus and imaginary numbers for this. You know, eleventeen, thirty-twelve and all those.

dhenderson01
Starting Member

4 Posts

Posted - 2006-05-28 : 15:54:44

Thanks for the example. I tried it and it works fine.
One question; what's DTb1? Is it Data Base1? I tried running with and w/o and it still runs with same results.

Don

PSamsig
Constraint Violating Yak Guru

384 Posts

Posted - 2006-05-28 : 17:24:11

DTbl is just a naming of the derived table (the sub select), its an l (like in Lima) and not a one. And you may be right in that it isnt needed here, its just an habbit.

-- This one's tricky. You have to use calculus and imaginary numbers for this. You know, eleventeen, thirty-twelve and all those.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-05-29 : 01:51:56

Read about aliases, subqueries and in sql server help file for more information

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources