sql server - Comparing 3 SQL Tables -

i trying compare 3 tables 1 being base table.

so here 3 table(s) table 1 base table , other 2 compared each other.

table1 id   | chargeitem ----------------- 5055 | item1 5056 | item2 5057 | item3 5058 | item4 5059 | item5 5060 | item6 5061 | item7 5062 | item8 5063 | item9 5064 | item10 5065 | item11  table2                                          id | membershiprecordid | chargeitemid | status ----------------------------------------------- 1  | 268765             | 5060         | 1      2  | 268765             | 5060         | 1       table3 id    | chargeitemid -------------------- 12146 | 5058 12146 | 5060 12146 | 5062 12146 | 5063 12146 | 5065 

here sql query far

select table1.id table1 t1  t1.id not in (      select table2.chargeitemid table2 t2       right join table3 t3 on t2.chargeitemid = t3.chargeitemid      t2.membershiprecordid = 268765 , t2.[status] = 2 ) 

so in sql query trying ids table 1 doesn't exist in table 2 , table 3. , inside sub query compare table 2 , table 3 table 2 taking priority on table 3 if chargeitemid exists in table 2 status = 2 fetch , return id along id's in table1

currently doesn't return id's table 1? suggestions why?

the result should following chargeitem id's returned table1

5055, 5056, 5057, 5059, 5061, 5064

hopefully explains issue?

thanks

update

please ignore t2.clubid = 1600 posted in error

update 2

expected result query

try using below query find out id's present on table1 , not present on table2 & table 3.

    create table table1 (id int);     insert table1 values (5055), (5056), (5060), (5065), (5057);      create table table2 (id int, chargeitemid int, status int)     insert table2 values (1, 5060,1)     insert table2 values (2, 5065,1)     insert table2 values (2, 5056,2)      create table table3 (id int, chargeitemid int )     insert table3 values (1, 5058)     insert table3 values (1, 5060)     insert table3 values (1, 5062)     insert table3 values (1, 5063)     insert table3 values (1, 5065)     insert table3 values (1, 5056)       select * table1     select * table2     select * table3      select id     table1     except     (         select chargeitemid table2         intersect         select chargeitemid table3     )