sql server 2008 r2 - Remove duplicate with separate column check TSQL -

i have 2 tables having same columns , permission records in it. 1 columns named isallow available in both tables. getting records of both tables in combine using union want skip similar records if isallow = 0 in 1 column - don't want records. union returns records , getting confused.

 below columns   isallow, userid, functionactionid 

i tried union gives both records. want exclude isallow = 0 in either table.

sample data table 1 isallow userid functionactionid 1         2           5 1         2           8  sample data table 2 isallow userid functionactionid 0         2           5 (should excluded) 1         2           15   

you can try this:

;with cte as(select *, row_number()                         over(partition userid, functionactionid order isallow desc) rn                            (select * table1               union               select * table2) t) select * cte rn = 1 , isallow = 1 

version2:

select distinct coalesce(t1.userid, t2.userid) userid,                  coalesce(t1.functionactionid, t2.functionactionid) functionactionid,                 1 isallow tabl1 t1 full join table2 t2 on t1.userid = t2.userid ,                         t1.functionactionid  = t2.functionactionid  (t1.isallow = 1 , t2.isallow = 1) or       (t1.isallow = 1 , t2.isallow null) or       (t1.isallow null , t2.isallow = 1)