CODEWITHSUNDEEP
sqlsql-server
Ashkan Mobayen Khiabani
Ashkan Mobayen Khiabani

Reputation: 34160

Union two selects removing duplicates base on some columns (not the full row)

I want to union 2 select statements that the result will be distinct record-wise however I want to omit duplicate results in second select statement (considering some columns) 

select id,name,[type],[parent] from table1 where [type] = 1 
union
select * from table2 // but exclude results from this table where
                     // a record with the same type and parent exists
                     // in the first select

I have thought of this (not tested):

select * from(
select *,rank() over(order by [type],[parent]) [rank] from(
  select id,name,[type],[parent] from table1 where [type] = 1 
  union
  select * from table2) t
 ) a where rank = 1

but it doesn't seem right, is there a better way to exclude duplicate from second select?

Edit:

each item can have add-ons. and add-ons are created in 2 ways:

1.specifically created add-ons in table1

2.publicly defining that an item of type x must have add-on

first select gets the list of addons that are specifically created for the Items , table2 create a list of add-ons for all the Items now there will be a duplicate add-on if there were an add-on specifically created for an Item.

Upvotes: 13

Views: 22109

Answers (3)

bat7
bat7

Reputation: 866

I just saw this question, and I think that instead of not exist you can use left join, it has better performance, so the query should look like below:

select * from table1
union
select * from table2
left join table1 exists
   on table2.parent = exists.parent 
   and table2.type = exists.type
where exists.id is null

Upvotes: 0

g2server
g2server

Reputation: 5367

try this:

;WITH cte AS (
   SELECT *, 1 AS SetID FROM table1 WHERE [Type] = 1
   UNION ALL
   SELECT *, 2 AS SetID FROM table2 
)

,cte2 as (
   SELECT *,
   RANK() OVER (PARTITION BY [Type], [Parent] ORDER BY SetID) FROM cte) rk
   FROM cte
)

SELECT * FROM cte2 WHERE rk = 1

Upvotes: 1

T I
T I

Reputation: 9933

try

select * from table1
union
select * from table2
where not exists(select 1 from table1 
                 where table2.parent = table1.parent 
                 and table2.type = table1.type)

Upvotes: 13

Related Questions