On Thu, Mar 23, 2000 at 12:14:51PM -0600, Kevin Heflin wrote:
>
> > What's wrong with *just* the first select?
>
> The first select only grabs projects which have subprojects assigned to
> them. I'm trying to figure out how to also grab projects which have no
> subprojects assigned to them yet.
Ah - I missed the (p1.project_id=s.projectid) in the where (s=subproject).
Do you need that bit of the where? You already have
sum(case when t.jobid=s.subproject_id then t.timespent else 0::float4 end)
^^^^^^^^^^^^^^^^^^^^^^^
Is that not enough? I obviously don't know how your database is meant to
work, but my guess is that a project with no subprojects would always have
t.jobid=s.subproject_id false, so you get your "0 as hoursworked". So, does
just the first query without
AND
(p1.project_id=s.projectid)
work?
if not, how about no extra and, and
sum(case when (t.jobid=s.subproject_id and p1.project_id=s.projectid) then t.timespent
else 0::float4 end)
?
Just guessing,
Patrick
