sql - Select from table based on result from other table -

firstly, db setup (access 2010) didn't create , cannot change:

violations:

packages:

i want select workpackagecodes when given role1 , role2 match record. example if role1 3 , role 2 8, want workpackagecodes id 3 , 8. if example role1 4 , role2 6, none should selected.

i achieved doing multiple queries want have in 1 single query (with/without subquery). need can in see if workpackageid in resultset other table. tried this:

//3 , 6 replaced parameters select role1 role tblsodviolations union select role2 tblsodviolations role1 = 3 , role2 = 6 

this gives me desired result , tried make 1 query, this:

select workpackagecode tblworkpackages workpackageid in (select role1 r tblsodviolations                         union                         select role2 tblsodviolations                         role1 = 3 , role2 = 6); 

but if run this, message:

this operation not allowed in subqueries.

so how can achieve this? or there way select workpackagecode in sort of sql if-statement? in advance!

try doing join:

select workpackagecode tblworkpackages wp join      tblsodviolations v      on wp.workpackageid in (v.role1, wp.workpackageid = v.role2) ,         v.role1 = 3 , v.role2 = 6; 

this might produce duplicates. if so,

select distinct workpackagecode tblworkpackages wp join      tblsodviolations v      on wp.workpackageid in (v.role1, wp.workpackageid = v.role2) ,         v.role1 = 3 , v.role2 = 6; 

however, unclear me why won't work:

select workpackagecode tblworkpackages wp wp.workpackageid in (3, 6); 

you can write this:

select workpackagecode tblworkpackages wp wp.workpackageid in (3, 6) ,       exists (select 1 tblsodviolations role1 = 3 , role2 = 6) 

this particular version comes closest way described need.