TransWikia.com

Run a query based on the values returned by another query

Stack Overflow Asked by Kevin D. on November 17, 2021

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'

3 Answers

I am not sure if this is something you are looking for..

I am assuming 'Process' is considered as Open order. Below query is not tested by the way:

Select a.WorkOrder, a.Date, a.Operation yourtable a WHERE a.Operation IN
(Select Operation from yourtable where Date ='1/4/2020') ;

Answered by The AG on November 17, 2021

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

Answered by The Impaler on November 17, 2021

(if you are on ORACLE database) You can use WITH clause to define a temporal table

WITH query1 as   -- query1 is the name of this temporal table 
(
    SELECT *
    FROM table
    WHERE Date = '1/4/2020'
)
SELECT *
FROM query1     -- you can do querys in query1 (the temporal table)
WHERE   WorkOrder  = '102' 
    AND Work Order = '103' 
    AND Work Order = '104'

Answered by Jose Jimenez on November 17, 2021

Add your own answers!

Ask a Question

Get help from others!

© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP