combine 2 queries to 1 result

Question

I have 2 queries those are as follows:

SELECT brt.RequestTypeID, brt.RequestTypeName, brs.RequestStepName AS PreviousStep 
FROM b_Steps bs
INNER JOIN b_RequestStep brs ON bs.RequestStepID = brs.RequestStepID 
INNER JOIN b_RequestType brt ON bs.RequestTypeID = brt.RequestTypeID

And the second one:

SELECT brt.RequestTypeID, brt.RequestTypeName, brs.RequestStepName AS NextStep 
FROM b_Steps bs 
INNER JOIN b_RequestStep brs ON bs.NextStepID = brs.RequestStepID 
INNER JOIN b_RequestType brt ON bs.RequestTypeID = brt.RequestTypeID

How would I combine 2 queries and return result like this:

| RequestTypeName | PreviousStep | NextStep      |
|------------------------------------------------|
| Buy good        | Login        | Add into cart |

the table b_Steps: RequestTypeID | RequestStepID | NextStepID (PK) — amateur.guy, Nov 23 '15 at 09:34

score 0 · Answer 1 · answered Nov 23 '15 at 09:26

0

try this out

select a.RequestTypeName, a.PreviousStep, b.NextStep
from
(SELECT brt.RequestTypeID, brt.RequestTypeName, brs.RequestStepName AS PreviousStep 
FROM b_Steps bs 
INNER JOIN b_RequestStep brs ON bs.RequestStepID = brs.RequestStepID 
INNER JOIN b_RequestType brt ON bs.RequestTypeID = brt.RequestTypeID) as a
inner join 
(SELECT brt.RequestTypeID, brt.RequestTypeName, brs.RequestStepName AS NextStep 
FROM b_Steps bs 
INNER JOIN b_RequestStep brs ON bs.NextStepID = brs.RequestStepID 
INNER JOIN b_RequestType brt ON bs.RequestTypeID = brt.RequestTypeID) as b
on a.RequestTypeID = b.RequestTypeID

answered Nov 23 '15 at 09:26

PK20

1,066
8
19

hj PK20, It is duplicated – amateur.guy Nov 23 '15 at 09:30
you want duplicates? Read more about UNION and UNION ALL [here](http://stackoverflow.com/questions/49925/what-is-the-difference-between-union-and-union-all) – PK20 Nov 23 '15 at 09:36

score 0 · Answer 2 · answered Nov 23 '15 at 10:41

seams to be an easy one but maybe i miss something:

SELECT brt.RequestTypeID, brt.RequestTypeName
,      brsp.RequestStepName AS PreviousStep 
,      brsn.RequestStepName AS NextStep 
FROM b_Steps bs
INNER JOIN b_RequestType brt ON bs.RequestTypeID = brt.RequestTypeID
LEFT JOIN b_RequestStep brsp ON bs.RequestStepID = brsp.RequestStepID 
LEFT JOIN b_RequestStep brsn ON bs.NextStepID = brsn.RequestStepID

i don't know if you have steps without a previous one or a next one, i used a left join in both cases.

That's exactly what I need. Thank you so much A ツ – amateur.guy Nov 24 '15 at 02:47 — amateur.guy, Nov 24 '15 at 02:47

combine 2 queries to 1 result

2 Answers2