PHP DB - A little SQL help

Author

A little SQL help

Bastien Koert

2008-01-31, 4:15 am

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

________________________________________
_________________________

Peter Westergaard

2008-01-31, 7:12 pm

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
>
>

Bastien Koert

2008-01-31, 7:12 pm

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 (includingevent > 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> > > > >
________________________________________
_________________________

Sponsored Links