[prev in list] [next in list] [prev in thread] [next in thread] 

List:       php-db
Subject:    [PHP-DB] RE: A little SQL help
From:       Bastien Koert <bastien_k () hotmail ! com>
Date:       2008-01-31 15:09:38
Message-ID: BAY135-W277E546A6D4348F85714C59D370 () phx ! gbl
[Download RAW message or body]


After some playing and a little sleep
 
This is the corrected query
 
 SELECT tour.event_start_date, tour.event_end_date, tour.event_name, CASE WHEN result \
IS NULL THEN 'N/A'ELSE resultEND FROM tourLEFT OUTER JOIN angler_results ON \
angler_results.tour_id = tour.record_idWHERE ( angler_results.angler_id =1OR \
angler_ID IS NULL  )
 
the goal was to produce this
 
 event_start_date event_end_date event_name result
NULL NULL Sunshine Show Down N/A 
NULL NULL Citrus Slam 1 
NULL NULL Lone Star Shootout N/A 
NULL NULL Battle on the Border N/A 
 
 
Thanks all,
 
Bastien
> Date: Thu, 31 Jan 2008 09:40:38 -0500> From: peter@westergaard.ca> To: \
> bastien_k@hotmail.com; php-db@lists.php.net> Subject: Re: A little SQL help> > \
> Bastien,> > I think it would help if you described what result you were expecting, \
> > what version of SQL you're using, and where your error is occurring. > > I'm \
> > going to assume you're looking for a list of tours (including event > start and \
> > end dates, event name, and result) for a specific angler (in > this case, angler \
> > 1). > > I personally would avoid putting the CASE logic into the SQL statement. > \
> > Just pull angler_results.result from the query and use PHP logic to > output \
> > "N/A" instead of the results if it's NULL, at output time, > especially if that's \
> > where you're having your problem.> > What problem are you actually encountering \
> > though?> > -P> > > >> > 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> > > > > 
_________________________________________________________________



[prev in list] [next in list] [prev in thread] [next in thread]