Join on a Join

[barnettjacob]

Guys, I've got the following code which returns the details of customer orders this year. The problem is that I need to return the $ sales number which is in the sales table with the complication being that the only way to link the two is through a 3rd table.

customerordernumber(column) is in the customerorderdetail and the customerordertransaction table.

reference1 (column) is in the customerordertransaction table and the sales table.

Where i've put xxxxxxxx in the code below I need to sum the sales (column) from the sales table.

Any ideas?
Regards
Jacob



select

DISTINCT c.customerordernumber,
sum((c.extendednetamount-c.extendedtaxamount))as ordertaken,
sum(quantity) as qty,
h.creationdate,
h.finalisedDate,
case when (sum(case when c.ordertype = 'O' then 1 else 0 end)) >0 then 'Order' else 'Layby' end as zType,
h.branchcode,
xxxxxxxxxxxxxxx

from eee..customerorderdetail c
join eee..customerorderheader h on h.customerordernumber=c.customerordernumber

where h.creationdate>='01/01/11'

group by c.customerordernumber,h.creationdate,h.finalisedDate,h.branchcode

order by c.customerordernumber,h.creationdate,h.finalisedDate,h.branchcode

 

[Qik3Coder]

join eee..customerorderheader h on
[!]c[/!].customerordernumber=[!]h[/!].customerordernumber
[!]join eee..customerordertransaction ct on
h.customerordernumber = ct.customerordernumber
[/!]
where h.creationdate>='01/01/11'


Need a better understanding of your table structure to actually do better than that.

Lodlaiden

You've got questions and source code. We want both!