March 17, 2009 at 2:36 am
create table samm(noo int,namee varchar(20))
insert into samm values(10,'R')
insert into samm values(20,'S')
insert into samm values(30,'T')
create table sammM(noo int,namee varchar(20))
insert into sammM values(40,'W')
insert into sammM values(50,'X')
insert into sammM values(10,'R')
SELECT * FROM samm
union
SELECT * FROM sammM
March 17, 2009 at 2:55 am
You need some identifier value to tell which records are from which table. It could be a column or a static value.
Ex: SELECT *,'A' FROM samm
union
SELECT *,'B' FROM sammM
"Keep Trying"
March 17, 2009 at 6:01 am
Hi,
Thanks for your reply..:)
Mentioned Query will return duplicate value
noo namee
----------- -------------------- ----
10 R A
10 R B
20 S A
30 T A
40 W B
50 X B
But i dont want duplicate row here
Please reply
March 17, 2009 at 6:32 am
rajendran.e (3/17/2009)
Hi,Thanks for your reply..:)
Mentioned Query will return duplicate value
noo namee
----------- -------------------- ----
10 R A
10 R B
20 S A
30 T A
40 W B
50 X B
But i dont want duplicate row here
Please reply
You would like to eliminate one of these two rows from the output:
10 R A
10 R B
Which row, and what is the rule for deciding?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
March 17, 2009 at 6:51 am
Here is one way of doing so:
select noo, namee, min(SourceName)
from (
select distinct noo, namee, 'a' as SourceName
from samm
union
select distinct noo, namee, 'b'
from sammM) dt
group by noo, namee
I assumed that it doesn't matter which one of the duplicate row won't be returned by the query, so my query won't return the duplicate rows that came from the second select statement in the union. If there is another logic that should be used, please specify it.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
March 17, 2009 at 6:57 am
As Chris pointed out the rule needs to be mentioned to eliminate the row but still here goes.....
select *,'A' from samm where noo <> all (select noo from #sammM)
UNION
select *,'B' from sammM
OR
select *,'A' from #samm
UNION
select *,'B' from #sammM where noo <> all (select noo from #samm)
"Keep Trying"
March 18, 2009 at 1:50 pm
There's another way to do this using the EXCEPT operator.
SELECT *, 'A' FROM samm
UNION ALL
SELECT *, 'B' FROM sammM
EXCEPT SELECT *, 'B'
FROM samm
The reason you have to use 'B' in the EXCEPT clause is to match all columns, including the extra column, from sammM to samm in order to exclude them.
Todd Fifield
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply