MYSQL - right join - where
-
Hello there guys, i trying to perform some right join to keep all records from tbl custumers, it works fine but when i do the "where" condition i noticed it killed my right join how can i solve this? i have a way to get the output i want but is very messy, so i guessed u guys could help me here, i sure is very easy X|
select Customers.cname as c_id, sum(Products.price*Purchases.quantity) as z
from Purchases
right outer join Customers on (Customers.CID = Purchases.cust)
left outer join Stores on (Stores.SID = Purchases.Stores)
left outer join Products on (Products.SKU = Purchases.prod)
###where Stores.city = "Porto"
group by Customers.cname
order by z desc(my aim is to show total value of purchaces per client in some store, but i need to include all client even those that didn´t shop (zero values)) thanks
nelsonpaixao@yahoo.com.br trying to help & get help
-
Hello there guys, i trying to perform some right join to keep all records from tbl custumers, it works fine but when i do the "where" condition i noticed it killed my right join how can i solve this? i have a way to get the output i want but is very messy, so i guessed u guys could help me here, i sure is very easy X|
select Customers.cname as c_id, sum(Products.price*Purchases.quantity) as z
from Purchases
right outer join Customers on (Customers.CID = Purchases.cust)
left outer join Stores on (Stores.SID = Purchases.Stores)
left outer join Products on (Products.SKU = Purchases.prod)
###where Stores.city = "Porto"
group by Customers.cname
order by z desc(my aim is to show total value of purchaces per client in some store, but i need to include all client even those that didn´t shop (zero values)) thanks
nelsonpaixao@yahoo.com.br trying to help & get help
Caveat I'm a SQL Server user but would assume the syntax is similar. Put the where clause into the join
left outer join Stores on (Stores.SID = Purchases.Stores and Stores.City = 'Porto')
Never underestimate the power of human stupidity RAH