sql - Manipulating Data in Joins -

diagram:

query join:

select distinct c.customerid, c.firstname , sh.duedate, p.productid,p.listprice saleslt.customer c    inner join saleslt.salesorderheader sh     on c.customerid = sh.customerid    inner join saleslt.salesorderdetail sd     on sh.salesorderid = sd.salesorderid   inner join saleslt.product p     on sd.productid = p.productid order listprice desc 

output:

desired result:

for desired output:

1. what add on existing query?
2. what optimized way of doing query ?
3. what time , space complexity sub query , join?

i think want:

    select c.customerid     , c.firstname      , sh.duedate     , max(p.productid) productid     ,p.listprice     saleslt.customer c        inner join saleslt.salesorderheader sh         on c.customerid = sh.customerid        inner join saleslt.salesorderdetail sd         on sh.salesorderid = sd.salesorderid       inner join saleslt.product p         on sd.productid = p.productid     group     , c.firstname      , sh.duedate     , p.listprice  order listprice desc 

not makes sense query, guess wanted know approach? have answer now, encourages put time answer if tick appropriate response,