sql server - SQL SELECT WHERE subquery (multiple rows) = subquery (multiple rows) -

forgive length, quite hard explain!

i've got 2 tables:

t_people ---------------------------------------- |  id       |  surname   |  forenames  | ---------------------------------------- |  1        |  baggins   |  frodo      | ---------------------------------------- |  2        |  took      |  peregrin   | ---------------------------------------- |  3        |  baggins   |  bilbo      | ----------------------------------------  t_courseresults -------------------------- |  personid |  result    | -------------------------- |  1        |  98.0%     | -------------------------- |  2        |  14.0%     | -------------------------- |  3        |  56.0%     | -------------------------- 

i execute following statement:

select result t_courseresults personid in (select id t_people surname '%b') 

...returning course results people surname beginning 'b' (2 rows).

now let's i've got third table stores static list of people (e.g. list of people in particular department)

t_staticlist ------------- |  personid | ------------- |  2        | ------------- |  3        | ------------- 

i want execute this:

select result t_courseresults (select personid t_staticlist) in (select id t_people surname '%b') 

which garbage, possible or being stupid? idea would return 1 row - bilbo baggins - name begins 'b' , in t_staticlist table. thanks!

you mean this?

select result t_courseresults personid in (   select p.id t_staticlist s   inner join t_people p on p.id = s.personid   p.surname '%b')