MySQL联表查询

Question

有三个表，用户表user,消息表message，文章表essay
现在想查询某个用户的基本信息和他的消息数目及文章数
譬如有26条消息，30篇文章，可我无论怎么查都是780条消息，780篇文章，也就是结果被相乘了……
以下是我试过的sql语句：
1.select u.name,u.id,u.avatar,count(e.essay_id) as essays_num,count(m.message_id) as messages_num from user as u,essay as e,message as m where u.id=123 and e.user_id=u.id and m.user_id=u.id
2.join也试过了，结果还是一样

Answer 1

select
u.id, u.name, mj.mn, ej.en
from user as u
left join (
select uid, count(message_id) as mn from message group by uid
) as mj on u.uid=mj.uid
left join (
select uid, count(essay_id) as en from essay group by uid
) ej on u.uid=ej.uid
where u.uid=1