Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2002
    Location
    bangalore
    Posts
    1

    Unanswered: join query not yielding required result

    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

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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/

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •