since the results table is not fully populates, the left outer join is used to 
take all the records from the tours table and include any matching records from 
the angler_results table. The CASE statement is used to auto fill the result of 
the results for that particular record set where there is no value from the 
angler_results table
 
Bastien> Date: Thu, 31 Jan 2008 10:16:23 +0100> From: [EMAIL PROTECTED]> To: 
[EMAIL PROTECTED]> CC: php-db@lists.php.net> Subject: Re: [PHP-DB] A little SQL 
help> > Not completely sure what type of result you expect, but here's one that 
makes sense to me.> > SELECT tour.record_id, tour.event_start_date, 
tour.event_end_date, tour.event_name, angler_results.result> FROM tour> LEFT 
JOIN angler_results> ON angler_results.tour_id = tour.record_id> AND 
angler_results.angler_id = 1> > I've taken out the CASE - I personally never 
worked with that and I'd probably put a default value of 'N/A' in the column 
angler_results.result. The DISTINCT has to go too, I'm guessing that the 
relation tour.record_id -> angler_results.tour_id is 1 -> *. The LEFT join CAN 
stay (as far as I know OUTER is only necessary when using ODBC or for 
maintaining compatibility with it, and then I'm still not sure what it does), 
if you want to have all rows in tour regardless of the join condition with NULL 
values for angler_results.result where tour.record_id is not in 
angler_results.tour_id. To leave out the NULL values use an INNER JOIN 
instead.> > Evert> > > Bastien Koert wrote:> > Hi All,> > > > Got myself stuck 
in a little sql here and can't seem to work out what I am doing wrong> > > > 
SELECT > > DISTINCT (tour.record_id), tour.event_start_date, 
tour.event_end_date, tour.event_name,CASE WHEN result is NULLTHEN 'N/A'ELSE 
angler_results.resultEND CASE > > FROM > > tourLEFT OUTER JOIN angler_results > 
> ON angler_results.tour_id = tour.record_idWHERE angler_results.angler_id =1> 
> > > where the table TOUR is as above in the primary part of the select and 
table ANGLER_RESULTS is (record_id, tour_id, angler_id, result)> > > > > > Any 
ideas?> > > > Bastien> > > > > > > > 
_________________________________________________________________> >> >> > > 
_________________________________________________________________

Reply via email to