CODEWITHSUNDEEP
sql-serversql-server-2012
balu
balu

Reputation: 99

How to get unique records based on 2 tables?

Hi I have doubt in sql server 

Table : emp 

    Id  |  Desc
    1   |  abc
    2   |  def
    3   | har

table2 : emp1

Id | Desc
3  | Har
4  | jai
4  | jai
5  | uou
6  | uni
6  | udkey
2  | Jainiu

based on above table I want output like below

 ID   | Desc
 1    | abc
 2    | def
 3    | har
 4    | jai
 5    | uou
 6    | uni

I tried like below

select  id, desc from emp
 union 
 select * from (select *,row_number()over(partition by id)as rn from emp1)
where rn=1

after executing this query I got an error like below

Msg 205, Level 16, State 1, Line 2
All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.

It's saying above 1st query are 2 column and 2 query are 3 column, this process how we avoid this rn column. please tell me how to write query to achive this task in sql server

Upvotes: 0

Views: 87

Answers (2)

mohan111
mohan111

Reputation: 8865

using the Union or Distinct we can reomve duplicates and row_number we can get output and In union should have equal number of columns DECLARE @emp TABLE ([Id] int, [Desc] varchar(3)) ;

INSERT INTO @emp
    ([Id], [Desc])
VALUES
    (1, 'abc'),
    (2, 'def'),
    (3, 'har')


DECLARE @emp1  TABLE 

    ([Id] int, [Desc] varchar(6))


INSERT INTO @emp1
    ([Id], [Desc])
VALUES
    (3, 'Har'),
    (4, 'jai'),
    (4, 'jai'),
    (5, 'uou'),
    (6, 'uni'),
    (6, 'udkey'),
    (2, 'Jainiu')


    ;with CTE AS (

      select 
id,
[Desc],
Row_NUMBER()OVER(PARTITION BY ID ORDER BY ID,[Desc]DESC)RN 
         from 

      (select distinct id,[Desc] from @EMP
    UNION ALL 
    select distinct id,[Desc] from @EMP1)T)

    select id,[Desc] from CTE 
    WHERE RN = 1

Upvotes: 0

Deep Kalra
Deep Kalra

Reputation: 1428

Use: 

SELECT id, 
       desc 
FROM   emp 
UNION 
SELECT id, 
       desc 
FROM   emp1

Union will automatically do a distinct and sort it in the proper order, you don't need to do anything, no windwos functions required

Result:

 ID   | Desc
 1    | abc
 2    | def
 2    | Jainiu
 3    | har
 4    | jai
 5    | uou
 6    | uni
 6    | udkey

If you want to remove udkey and Jainiu please mention a logi to choose between def and Jainiu and so on...

Upvotes: 1

Related Questions