title: Union two selects removing duplicates base on some columns (not the full row)
body: <p>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) </p>
  
  <pre><code>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
  </code></pre>
  
  <p>I have thought of this (not tested):</p>
  
  <pre><code>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
  </code></pre>
  
  <p>but it doesn't seem right, is there a better way to exclude duplicate from second select?</p>
  
  <p>Edit:</p>
  
  <p>each item can have add-ons. and add-ons are created in 2 ways:</p>
  
  <p>1.specifically created add-ons in table1</p>
  
  <p>2.publicly defining that an item of type x must have add-on </p>
  
  <p>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. </p>