Chris Austin wrote:
>
> hi there,
>
> I am having trouble with some sql and outer joins. Below is the sql statement I have
> so far:
>
> SELECT p.projectid,a.num_hrs,b.num_hrs,c.num_hrs
> FROM projects AS p LEFT JOIN daily AS a ON p.projectid = a.projectid,
> projects AS p1 LEFT JOIN daily AS b ON p1.projectid = b.projectid,
> projects AS p2 LEFT JOIN daily AS c ON p2.projectid = c.projectid
> WHERE a.recdate = '1999-06-21'
> AND b.recdate = '1999-06-22'
> AND c.recdate = '1999-06-23'
> AND b.projectid = p.projectid
> AND c.projectid = p.projectid
>
> My problem, if there is data for all the dates then I get the correct format
> but if one date does not have data then I get back a empty set. I need it to
> return a recordset close to the one below:
>
> +-----------+---------+---------+---------+
> | projectid | num_hrs | num_hrs | num_hrs |
> +-----------+---------+---------+---------+
> | CG-006 | 6 | 4 | NULL |
> | CG-029 | 2 | 6 | NULL |
> +-----------+---------+---------+---------+
>
> The nulls would be there because there is no data for the 23rd but I still need the
> column
> to return with null values. Any insight would be appreciated. Thanks in advance.
>
> -chris
Hi Chris
If you only want always all above columns, then you have to keep only one projects table
like:
SELECT
p.projectid
,a.num_hrs
,b.num_hrs
,c.num_hrs
FROM
projects AS p
LEFT JOIN daily AS a ON p.projectid = a.projectid
LEFT JOIN daily AS b ON p.projectid = b.projectid
LEFT JOIN daily AS c ON p.projectid = c.projectid
WHERE
a.recdate = '1999-06-21'
AND b.recdate = '1999-06-22'
AND c.recdate = '1999-06-23'
Tschau
Christian
PS: Sorry for the late answer, I was on vacation.
