I believe what you want is called a "left outer join": include all records in one table (projects) and records that match it on the joined tables (all the others). Try something like this (UNTESTED): SELECT p.project_id, p.project_title, SUM(t.timespent) FROM project p, subproject s, timelog t WHERE p.project_id *= s.projectid AND t.jobid = s.subproject_id GROUP BY p.project_id; (assuming a project id and a project title are one to one, there's no need to group by both of them.) --------------------------------------------------------------------- Andrew J. Perrin - [EMAIL PROTECTED] - NT/Unix Admin/Support Department of Demography - University of California at Berkeley 2232 Piedmont Avenue #2120 - Berkeley, California, 94720-2120 USA http://demog.berkeley.edu/~aperrin --------------------------SEIU1199 On Thu, 16 Mar 2000, Kevin Heflin wrote: > > I have the following query which will list projects, which totals up the > timespent on jobs > > select p.project_id, p.project_title, > sum(case when t.jobid=s.subproject_id then t.timespent else 0::float4 end) > FROM project p, subproject s, timelog t > where p.project_id=s.projectid and t.jobid=s.subproject_id > GROUP BY p.project_id, p.project_title; > > This works great assuming that a 'project' has 'jobs' assigned to it. > and that one of those jobs has had 'timespent' on it... > > so if a project does not yet have jobs assigned to it, and therefor no > timespent on it.. it doesn't show up in the results. > > Is there anyway around this? in otherwords, I would like to retrieve a > complete list of projects regardless of whether or not it has jobs > assigned to it.. > > Kevin > > > > > > > -------------------------------------------------------------------- > Kevin Heflin | ShreveNet, Inc. | Ph:318.222.2638 x103 > VP/Production | 333 Texas St #175 | FAX:318.221.6612 > [EMAIL PROTECTED] | Shreveport, LA 71101 | http://www.shreve.net > -------------------------------------------------------------------- >
