join left on多张表,SQL多个COUNT（）从两个表，在LEFT JOIN

i have three tables, i want to select all data from one of the tables, and also collect a COUNT of how many times that specific row of data is linked in the other two tables.

So, SELECT all data from site_projects. Then return a COUNT of site_project_members WHERE site_projects.id = site_project_members.pid and also return a COUNT of site_project_tasks WHERE site_projects.id = site_project_members.pid

I hope i'm making sense, to the query, it looks correct. and it querys the database (MySQL) with no problems. Except it returns a sum of both of the counts as both things. (see below the table structures)

site_projects

id | title | desc | start | deadline | progress

1 | Project 1 | a project | 1321748906 | 1329847200 | 20

site_project_members

id | pid | uid | img | hidden

1 | 1 | 1 | 1 | 0

2 | 1 | 2 | 2 | 0

site_project_tasks

id | pid | desc | completed

1 | 1 | Task 1 | 1

1 | 1 | Task 2 | 0

Here is my query:

SELECT p.`id`, p.`title`, p.`desc`, p.`progress`, p.`start`, p.`deadline`, COUNT(m.`id`) as `members`, COUNT(t.`id`) as `tasks` FROM `site_projects` p LEFT JOIN `site_project_members` m ON p.`id`=m.`pid` LEFT OUTER JOIN `site_project_tasks` t ON p.`id`=t.`pid` ORDER BY p.`id` ASC

The result i get is:

id | title | desc | progress | start | deadline | members | tasks

1 | Project 1 | a project | 20 | 1321748906 | 1329847200 | 4 | 4

Both of the "4" values should be 2. however they are not :S Anyone able to help?

Much Appreciated

Thanks,

Dan

解决方案SELECT p.id , p.title , p.desc , p.progress , p.start , p.deadline ,

COALESCE( m.cnt, 0 ) AS members,

COALESCE( t.cnt, 0 ) AS tasks

FROM site_projects p

LEFT JOIN

( SELECT pid, COUNT(*) AS cnt FROM

site_project_members

GROUP BY pid ) m

ON p.id = m.pid

LEFT JOIN

( SELECT pid, COUNT(*) AS cnt FROM

site_project_tasks

GROUP BY pid ) t

ON p.id = t.pid

ORDER BY p.id ASC