Join on a Join - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Join on a Join

Author

Topic

barnettjacob
Starting Member

9 Posts

Posted - 2011-08-24 : 08:45:50

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

Kristen
Test

22859 Posts

Posted - 2011-08-24 : 10:01:33

I'm maybe missing something.

Can you not just JOIN the additional table (or use LEFT JOIN if rows may not exist for some customers) and then SUM() the relevant column from that additional table?

Note that you don't need DISTINCT as you already have a GROUP BY

barnettjacob
Starting Member

9 Posts

Posted - 2011-08-24 : 10:12:41

Kristen, if I dont use distinct then the following line doesnt do what I want as each order number can have several entries, some of which have status 'o' and some of 's'.

As for the join, I just don't know how to do it seeing as there are no common columns between the table I am using and the sales table.

Kristen
Test

22859 Posts

Posted - 2011-08-24 : 10:30:33

Well hopefully someone else can explain to me how DISTINCT in a statement with GROUP BY can alter the outcome.

"As for the join, I just don't know how to do it seeing as there are no common columns between the table I am using and the sales table"

You can JOIN additional tables to get the relationship you want:


FROM TableA AS A
     JOIN TableB AS B
         ON B.SomeID = A.SomeID
     JOIN TableC AS C
         ON C.SomeOtherID = B.SomeOtherID

provided that the additional tables do not have multiple rows that match - otherwise you aggregate functions will include duplicate values.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources