Multiple INNER JOINs with GROUP BY and Aggregate

Question

I came back with another question. I’ve been doing this for a day and a half and still no luck. Therefore, I have the tables below.

Table1 Field1 Field2 Field3 Field4 Field5 DR1 500 ID1 Active TR1 DR2 250 ID2 Active TR1 DR3 100 ID1 Active TR1 DR4 50 ID3 Active TR1 DR5 50 ID1 Cancelled TR1 DR6 150 ID1 Active TR2 Table2 Field1 Field3 ID1 Chris ID2 John ID3 Jane Table3 Field1 Field2 TR1 Shipped TR2 Pending 

Currently, I can achieve this result.

 Name Total Chris 650 3 John 250 1 Jane 50 1 

using this sql statement

 SELECT t2.Field3 as Name , SUM(t1.Field2) as Total FROM [Table1] t1 INNER JOIN [Table2] t2 ON t1.Field3 = t2.Field1 GROUP BY t2.Field3 

However, I would like to get this result, shown below.

 Chris 600 2 John 250 1 Jane 50 1 

First I want to check Table3, if it has a “sent” Field2, then it includes everything in table 1 with the “Active” Field4 field. It should not include the Canceled field 4. And if table 3 has a Pending field, it should also not include it. I would appreciate any little help. Thanks.

Answer 1

Well, you just need to join all the tables needed to have fields in the where clause.

And add the where clause.

 SELECT t2.Field3, SUM(t1.Field2) as CTotal, count(*) as NbItems FROM Table1 t1 INNER JOIN Table2 t2 ON t1.Field3 = t2.Field1 --add a join on Table3 INNER JOIN Table3 t3 ON t3.Field1 = t1.Field5 --add your where clause WHERE t1.Field4 = 'Active' AND t3.Field2 <> 'Pending' GROUP BY t2.Field3 --ORDER BY CTotal DESC 

see SqlFiddle

EDIT

For your problem

 SELCT custInfo.CName, Count(*) as TransactionCount, SUM(CTotal) as TransactionTotal FROM (TamarawTransaction trans INNER JOIN TamarawCustomerInformation custInfo ON trans.CCustomerCode=custInfo.CCustomerCode) INNER JOIN TamarawTrip trip ON trans.CTrip=trip.CTrip Where trip.CStatus='Finalized' AND trans.CStatus='Active' GROUP BY custInfo.CName 

With "weird access connection syntax"

 SELECT TamarawCustomerInformation.CName, Count(*) AS TransactionCount, SUM(CTotal) AS TransactionTotal FROM TamarawCustomerInformationn INNER JOIN (TamarawTrip INNER JOIN TamarawTransaction ON TamarawTrip.CTrip=TamarawTransaction.CTrip) ON TamarawCustomerInformationn.CCustomerCode=TamarawTransaction.CCustomerCode WHERE TamarawTrip.CStatus='" & "Shipped" & "' and TamarawTransaction.CStatus='" & "Active" & "' GROUP BY TamarawCustomerInformation.CName;