RE: [PHP-DB] A little SQL help
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> > > > > > > > _> >> >> > > _
RE: [PHP-DB] A little SQL help
sorry, went to bed getting a sql error 1064 error in syntax bastien> Date: Thu, 31 Jan 2008 15:48:02 +1100> From: [EMAIL PROTECTED]> To: [EMAIL PROTECTED]> CC: php-db@lists.php.net> Subject: Re: [PHP-DB] A little SQL help> > 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)> > Are you getting an error?> > Are you getting the wrong results?> > Something else?> > -- > Postgresql & php tutorials> http://www.designmagick.com/ _
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 _ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] A little SQL help
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) Are you getting an error? Are you getting the wrong results? Something else? -- Postgresql & php tutorials http://www.designmagick.com/ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] A little SQL help
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 _
