[Paramesh]

hi,
i have three tables namely tblProjects,tblModule,tblMember. i have to select number of modules and members in each project.i have the following query,


select a.projectid, a.projectname,count(distinct b.moduleid)as module_count,count(distinct c.memberid)as member_count from
tblprojects a left outer join tblmodule b on (a.projectid=b.projectid)left outer join tblmembers c on (a.projectid=c.projectid)
group by a.projectid,a.projectname;


This query works fine in oracle,but did not give the required result in MySql. the problem is that ,if there exists a project in tblProjects table
which does not have any modules in tblModule table ,still it gives me a count one.

please help me,
param

[r937]

the problem appears to be that COUNT DISTINCT returns 1 when the only values counted are all NULLs

the reason you need to use DISTINCT in the first place is because you have a double join that acts like a cross join -- for a given project, each of its modules is joined with every single member, hence you need to count the distinct values of each table's primary key

split the query into two left outer joins, one to modules and the other to members

select a.projectid, a.projectname
, sum(case when moduleid is null then 0 else 1 end)
as module_count
from tblprojects a
left outer join tblmodule b
on a.projectid=b.projectid
group by a.projectid, a.projectname

select a.projectid, a.projectname
, sum(case when memberid is null then 0 else 1 end)
as member_count
from tblprojects a
left outer join tblmembers c
on a.projectid=c.projectid
group by a.projectid, a.projectname


rudy
http://rudy.ca/