to all guru
I'm having problem in query using left join which resulting into a double output
here is my table
customer
location
review
when i remove the left join location i got the result that i wanted in review table
the location consist of coordinates of zipcode
the review stored value good / always good / bad (something like that )in which user will rate a certain company
here is a query which work using customer and review table
select customer.zip, customer.company, customer.id
sum(review.rate),
count(review.customerid)
from customer
left join (select * from review where status='T') as rev on customer.id=rev.customerid
where customer.zip in (90210)
and customer.status='A'
group by customer.id
-- so this query works perfect
now when i add the table location the sum(review.rate) is also doubled eg: instead of output is 10 it became 20
when i look up at the table location
there are two rows in zipcode=90210 but they are different city
-- here is the query
select customer.zip, customer.company, customer.id,
location.latitude,location.longitude,
sum(review.rate) ,
count(review.customerid)
from customer
left join (select * from review where status='T') as rev on customer.id=rev.customerid
left join location on (location.zipcode=customer.zip)
where customer.zip in (90210)
and customer.status='A'
group by customer.id ,location.zipcode
// note that i also added location.zipcode for grouping
thanks in advance
