Sum with Inner and Left Joins 5

[trenttc]

I want to change this so orders.total is a sum by order.id Something with "sum(orders.total) as total". How do I change this?

select c.name
from cust c
inner join orders
on c.id=orders.id
left join flags
on c.id=flags.id
where orders.order_date > '1/1/2007'
and orders.total>20
and flags.flag_1=1
and c.price_cat='01'

 

[psperry2]

The select statement will look something like this:

Select sum(order.total) As Ototal
from Cust c
Inner JOIN Orders O ON c.id=O.id
LEFT JOIN Flags F ON C.id=F.id
WHERE O.Order_date > '1/1/2007'
AND O.total>20
AND f.flag_1 =1
AND c.price_cat='01'
GROUP BY O.id

 

[bborissov]

select c.name, Orders.Total
from cust c
inner join (SELECT Id, SUM(Total) AS Total
                   FROM orders
                   where orders.order_date > '20070101'
            GROUP BY Id) Orders  on c.id=orders.id
left join flags  on c.id=flags.id
WHERE orders.total > 20 and
      flags.flag_1 = 1  and
      c.price_cat  = '01'

not tested

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
Microsoft MVP VFP

 

[SQLDenis]

those left joins look incorrect to me, some of the stuff which is in the WHERE clause needs to go in the AND or it will be the same as a inner join

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[trenttc]

Not sure you mean by "stuff which is in the WHERE clause needs to go in the AND ..." Can you explain?

 

[r937]

select c.name
     , ordertotals.Total
  from cust c
inner 
  join ( SELECT Id
              , SUM(Total) AS Total
           FROM orders
          where order_date > '20070101'
         GROUP 
             BY Id 
         [red]HAVING SUM(Total) > 20[/red]
       ) as ordertotals
    on ordertotals.Id = c.id
left outer
  join flags  
    on flags.id = c.id
   [blue][b]and flags.flag_1 = 1[/b][/blue] 
WHERE c.price_cat  = '01'

r937.com | rudy.ca

 

[SQLSister]

Rudy showed you the code differnce, but let me explain why it needs to happen.

Whn you use a left join and you need to limit the records in that table to a subset of the total records inthe table, you need to put that limitation in the join. If you put it inthe where clause instead then it will only pick out records that meet that condition as well as the join, so you never get the records where the left side of the join has a record and the right side does not.

Take a simple example:

Table1
field1 field2
1 test
2 test2
3 test3

table2
field3 field4
1 0
2 1

NOw look at the code and results of the next two queries

select * from table1 t1
left join table2 t2 on t1.field1 = t2.field3
where t2.field4 = 0

this will return
field1 field2 field3 fieldd4
1 test 1 0

select * from table1 t1
left join table2 t2 on t1.field1 = t2.field3
and t2.field4 = 0

Do you see why it makes a differnce where you put the condition?

this will return
field1 field2 field3 fieldd4
1 test 1 0
2 test2 null null
3 test3 null null

"NOTHING is more important in a database than integrity." ESquared

 

[jbenson001]

Great Explination SQLSister . .thanks

 

[ESquared]

I've always loved a good "explination.

 

[jbenson001]

Are you a school teacher .. you have to remember.. there is no spell check here....

 

[ESquared]

No, no, don't take it negatively. I was just enjoying things. Thanks for brightening my day!

 

[jbenson001]

Glad I could help.