PSVSupporter PSVSupporter - 9 months ago 36
SQL Question

How do I create this output in SQL

I have this information.

Table OrderTotals

Order Value
A 10
B 20
C 15

Table Orderdetails

Order Line Description
A 11 Red
B 24 Blue
B 25 Green
B 28 Yellow
C 17 Green

And I want this output

Order Line Description Value
A 11 Red 10
B 24 Blue 20
B 25 Green
B 28 Yellow
C 17 Green 15

Is this possible? And if yes, how?

Answer Source

You would seem to want a left join:

select od.*,
       (case when row_number() over (partition by od.[order] order by line) = 1
             then ot.value
        end) as value
from orderdetails od left join
     ordertotals ot
     on od.[order] = ot.[order]
order by od.[order], od.line;

I'm not sure why you would want value on only one line, but this should achieve that goal.