List:General Discussion« Previous MessageNext Message »
From:Christian Mack Date:July 15 1999 6:18pm
Subject:Re: outer joins
View as plain text  
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.

Thread
outer joinsChris Austin23 Jun
  • Re: outer joinsBenjamin Pflugmann23 Jun
  • Re: outer joinsChristian Mack15 Jul