Combining results of 2 queries 1

[LadyLin]

I have 2 queries, the first

SELECT 
O.WORKORDER_BASE_ID as O_BASE, 
O.SEQUENCE_NO AS O_SEQ,    
COALESCE(OP_R.RESOURCE_ID,O.RESOURCE_ID) AS O_RES ,
FROM 
OPERATION O
LEFT OUTER JOIN 
OPERATION_RESOURCE OP_R 
ON 
O.WORKORDER_BASE_ID = OP_R.WORKORDER_BASE_ID
AND O.SEQUENCE_NO = OP_R.SEQUENCE_NO
WHERE
O.WORKORDER_BASE_ID LIKE 'CR-AG01763%'
ORDER BY 
O.WORKORDER_BASE_ID,
O.SEQUENCE_NO

the results look like:
CR-AG01763 10 SALES
CR-AG01763 20 ENG
CR-AG01763 20 QC
CR-AG01763 30 PUR
CR-AG01763 40 ENG

the 2nd query

SELECT 
L.WORKORDER_BASE_ID AS L_BASE, 
L.OPERATION_SEQ_NO AS L_SEQ, 
L.RESOURCE_ID AS L_RES,
Sum(L.GOOD_QTY) AS L_QTY
FROM LABOR_TICKET L
WHERE L.WORKORDER_BASE_ID LIKE 'CR-AG01763%'
GROUP BY 
L.WORKORDER_BASE_ID, 
L.OPERATION_SEQ_NO, 
L.RESOURCE_ID

the results

CR-AG01763 10 SALES 1.0
CR-AG01763 20 QC 0.25
CR-AG01763 30 PUR 1.0

I want the end result to be

CR-AG01763 10 SALES 1.0
CR-AG01763 20 ENG 0 <<<<< no labor tickets for this
CR-AG01763 20 QC 0.25
CR-AG01763 30 PUR 1.0
CR-AG01763 40 ENG 0 <<<<< no labor tickets for this

Is there a way to join these 2 queries to accomplish it?

 

[vongrunt]

Add 0 AS O_QTY in first query. Remove ORDER BY
Do UNION ALL with another query
Sort everything.

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[vongrunt]

Ack... I thought you want vertically concatenated rows. This does as you wanted:

SELECT O.*, ISNULL(O.L_RES, 0) as L_RES
FROM
(	 <first query here, WITHOUT ORDER BY>
) O
LEFT OUTER JOIN 
(	<second query here>
) L
ON O.O_BASE = L.L_BASE
	AND O.O_SEQ = L.L_SEQ
	AND O.O_RES = L.L_RES
ORDER BY O.WORKORDER_BASE_ID,
	O.SEQUENCE_NO

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[LadyLin]

I used your 1st suggestion and got what I needed, (I think)
After reading your 2nd post, I'll have to go back and retry
with that suggestion, Monday though, my brain has had enough work this week and there's basketball to watch! Thanks again for the prompt response, now I won't be thinking of it all weekend! And here's another star!

 

[LadyLin]

I finally used a combination of your suggestions.

SELECT L_BASE, L_SEQ, L_RES, SUM(L_QTY)
FROM

(SELECT 
L.WORKORDER_BASE_ID AS L_BASE, 
L.OPERATION_SEQ_NO AS L_SEQ, 
L.RESOURCE_ID AS L_RES,
Sum(L.GOOD_QTY) AS L_QTY
FROM LABOR_TICKET L
WHERE L.WORKORDER_BASE_ID LIKE 'CR-AG01763%'
GROUP BY 
L.WORKORDER_BASE_ID, 
L.OPERATION_SEQ_NO, 
L.RESOURCE_ID

UNION ALL

SELECT 
O.WORKORDER_BASE_ID as O_BASE, 
O.SEQUENCE_NO AS O_SEQ,    
COALESCE(OP_R.RESOURCE_ID,O.RESOURCE_ID) AS O_RES ,
0 AS O_QTY
FROM 
OPERATION O
LEFT OUTER JOIN 
OPERATION_RESOURCE OP_R 
ON 
O.WORKORDER_BASE_ID = OP_R.WORKORDER_BASE_ID
AND O.SEQUENCE_NO = OP_R.SEQUENCE_NO
WHERE
O.WORKORDER_BASE_ID LIKE 'CR-AG01763%'
AND O.STATUS = 'R') DT
	
GROUP BY
L_BASE,
L_SEQ,
L_RES

That gave me the results, I reversed the order and then summarized. Worked well, Thanks again.