Run a query based on the values returned by another query

Consider the tables:

WorkOrder     Date
   101       1/2/2020
   101       1/2/2020
   102       1/2/2020
   102       1/3/2020
   101       1/3/2020
   103       1/4/2020
   104       1/4/2020
   104       1/5/2020
   103       1/5/2020
   104       1/5/2020
   102       1/5/2020
WorkOrder    Operation
   101        Process
   101        Run
   102        Process
   102        Run
   101        Ship
   103        Process
   104        Process
   104        Run
   103        Run
   104        Ship
   102        Ship

If I were to run query1:

SELECT *
FROM table1
JOIN table2 on table1.WorkOrder = table2.WorkOrder 
WHERE Date = '1/4/2020'

I want a query that would return all of the rows for open work orders in that date range (i.e. include the process, run, and ship). This would be the equivalent to what I want returned:

SELECT *
FROM table
JOIN table2 on table1.WorkOrder = table2.WorkOrder 
WHERE WorkOrder = '102' AND Work Order = '103' AND Work Order = '104'

Therefore the desired output would be:

   102          Process    1/2/2020
   102           Run       1/3/2020
   103          Process    1/4/2020
   104          Process    1/4/2020
   104           Run       1/5/2020
   103          Ship       1/5/2020
   104          Ship       1/5/2020
   102          Ship       1/5/2020

But I don’t want to specify each of the work orders returned from query1. Also, how could this work with a date range:

SELECT *
FROM table
JOIN table2 on table1.WorkOrder = table2.WorkOrder 
WHERE Date <= 'X' AND Date >= 'Y'

Answer

If you want all the rows for work orders that:

• started on or before that date, and
• ended on of after that date, and
• include all three operations (process, run, and ship).

You can do:

select *
from t
where workorder in (
  select a.workorder
  from t a
  join t b on b.workorder = a.workorder
  join t c on c.workorder = a.workorder
  where a.operation = 'Process'
    and b.operation = 'Run'
    and c.operation = 'Ship'
    and a.date <= '2020-01-04'
    and c.date >= '2020-01-04'
) x