Reuse query results in a different query

Question

I have 2 queries that need to run one after the other one. the first one returns a single value for work order and the next one returns all related serial numbers:

--Get the work order
SELECT * FROM WorkOrder w WHERE w.WorkOrderName = 'test wo';
--Get related serial numbers
SELECT * FROM SerialNumber WHERE SerialNumber.WorkOrderId = w.Id;

Is it possible to run both queries in the same context so that I could use the w variable in the second query and get 2 result sets:
enter image description here

possible duplicate of [Querying the Result set of a Previous Query](http://stackoverflow.com/questions/29362693/querying-the-result-set-of-a-previous-query) — Zohar Peled, Apr 19 '15 at 13:10

score 1 · Answer 1 · answered Apr 19 '15 at 11:34

1

Use IN (or EXISTS or JOIN):

SELECT sn.*
FROM SerialNumber sn
WHERE sn.WorkOrderId IN (SELECT w.WorkOrderId
                         FROM WorkOrder w JOIN
                              Product p
                              ON p.Id = w.ProductId
                         WHERE w.WorkOrderName = 'test wo'
                        );

If you want all columns in the first table plus the serial number, just use join:

SELECT w.WorkOrderId
FROM WorkOrder w JOIN
     Product p
     ON p.Id = w.ProductId JOIN
     SerialNumber sn
     ON sn.WorkOrderId = w.WorkOrderId
WHERE w.WorkOrderName = 'test wo';

Note: I am guessing what the id column in WorkOrder is. Also, you should list out the columns you want instead of using SELECT *.

answered Apr 19 '15 at 11:34

Gordon Linoff

1,242,037
58
646
786

thanks for your help but as I written I need to get 2 results: a work order and its serial numbers in two different results sets. the first query you provided will only return the serial numbers and the second will return one result for the entire data – Yoav Apr 19 '15 at 11:43
@Yoav . . . The answer is "not exactly". You can create a temporary table with the results of the first query, and then query that again in the second and third. How you do that depends both on the database you are using and on how your application communicates to the database. For instance, in many databases, temporary tables are tied to the session, so if you start a new session for each query, then temporary tables won't quite fit the bill. – Gordon Linoff Apr 19 '15 at 12:03

score 0 · Answer 2 · answered Apr 19 '15 at 11:58

You can use variables

declare @WorkOrderId int -- Or what ever type it is

--Get the work order
SELECT @WorkOrderId = w.Id FROM WorkOrder w WHERE w.WorkOrderName = 'test wo';

--Get related serial numbers
SELECT * FROM SerialNumber WHERE SerialNumber.WorkOrderId = @WorkOrderId;

Daniel · Answer 3 · 2015-04-19T12:31:24.037

SELECT * FROM WorkOrder w 
WHERE w.WorkOrderName = 'test wo';

SELECT sn.* FROM SerialNumber sn
 JOIN WorkOrder wo
  ON sn.WorkOrderId = wo.Id
 WHERE wo.WorkOrderName = 'test wo';

Alternatively you could use a temp table....

IF OBJECT_ID('tempdb..#wo') IS NOT NULL DROP TABLE #wo

SELECT * INTO #wo FROM WorkOrder w 
WHERE w.WorkOrderName = 'test wo';

SELECT sn.* FROM SerialNumber sn
 JOIN #wo wo
  ON sn.WorkOrderId = wo.Id

score 0 · Answer 4 · answered Apr 19 '15 at 12:27

If you want both results and also use the w.Id in the second query, you need to have a field in your final result to identify the result set, Something like this:

SELECT 'Results 1' As resultSet, Id As Col1, WorkOrderName As Col2, Created As Col3
FROM WorkOrder w
WHERE w.WorkOrderName = 'test wo'
UNION ALL
SELECT 'Results 2' As resultSet, Id As Col1, WorkOrderId As Col2, SerialValue As Col3
FROM SerialNumber 
WHERE SerialNumber.WorkOrderId IN (SELECT w.Id
                FROM WorkOrder w 
                WHERE w.WorkOrderName = 'test wo')

Reuse query results in a different query

4 Answers4