Trouble with join syntax

pederdin

I have a join syntax which looks like this

select
m.custid,
m.name,
e.email,
count(i.to_id) as mess,
count(p.friend_id) as friends
from member m,member_email e
left join private_friends_pending p on (m.custid=p.friend_id and p.status=1)
left join private_id i on (m.custid=i.to_id and i.to_viewed=0)
where m.accepted=1 AND e.id=m.email
group by m.custid
order by mess desc

This gives me five columns:
custid, name, email, mess,friends

If a customer has either zero messages or zero friends it works fine. But if they have more than zero in both columns (mess and friends) it adds the total of amount of friends and messages and shows the total in both columns. So I can't tell that a certain customer has 2 messages and 4 friends.

Does anyone have a solution?

bastien

select
m.custid,
m.name,
e.email,
count(i.to_id) as mess,
count(p.friend_id) as friends
from member m,member_email e
left join private_friends_pending p on (m.custid=p.friend_id )
left join private_id i on (m.custid=i.to_id )
where m.accepted=1 AND e.id=m.email and p.status=1 and i.to_viewed=0
group by m.custid
order by mess desc

the and parts should be part of the where clause not the join

pederdin

That gives me the same result as earlier, except that I only get the results that have both friends and messages.

This gives me the same result as inner join actually.