SQL:Using group by to check existence of a value against a separate list of strings

Question

I am trying to group by OrderID and lineID and return only the unique orderID and lineID from groups that include any values in a separate list of strings.

Here is some sample data.

 OrderID        lineID  positionID   fieldOfInterest    somefield1   somefield2 ....
   1A2          10248       1          '10xa'              -           -
   1A2          10248       2          '42-vf'             -           -
   1A2          10248       3          '10xb'              -           -
   1A2          10249       2          '10xa'              -           -
   1C3          11200       5          '10002'             -           -
   1C3          10250       1          'N52O'              -           -
   1V8          10250       7          'fas01'             -           -
   1V8          10250       8          '10002'             -           -
   .             .          .           .                  .           .
   .             .          .           .                  .           .
   .             .          .           .                  .           .
separate list to search groups against: ('10xa','10002')

Result that I am looking for would be

  OrderID        lineID  
    1A2          10248       <------ group contained '10xa' 
    1A2          10249       <------ group contained '10xa' 
    1C3          11200       <------ group contained '10002'
    1V8          10250       <------ group contained '10002'

I have tried

having (min(fieldOfInterest) in ('10xa','10002')) or (max(fieldOfInterest) in ('10xa','10002')) 

but I don't think this will work all the time. Maybe there is some sort of join I can do on the list of strings and the table.

Answer 1

In Postgres, I would recommend aggregation and bool_or() :

select orderID, lineID
from mytable
group by orderiD, lineID
having bool_or(fieldOfInterest in ('10xa','10002'))