Union two selects removing duplicates base on some columns (not the full row)
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)
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