A little SQL help

--_3a65ad3a-e876-45d0-8055-01221c0db4c9_
Content-Type: text/plain; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable


Hi All,
=20
Got myself stuck in a little sql here and can't seem to work out what I am =
doing wrong
=20
SELECT=20
DISTINCT (tour.record_id), tour.event_start_date, tour.event_end_date, to=
ur.event_name,CASE WHEN result is NULLTHEN 'N/A'ELSE angler_results.resultE=
ND CASE=20
FROM=20
tourLEFT OUTER JOIN angler_results=20
ON angler_results.tour_id =3D tour.record_idWHERE angler_results.angler_id =
=3D1
=20
where the table TOUR is as above in the primary part of the select and tabl=
e ANGLER_RESULTS is (record_id, tour_id, angler_id, result)
=20
=20
Any ideas?
=20
Bastien
=20
=20
=20
____________________________________________________________ _____


--_3a65ad3a-e876-45d0-8055-01221c0db4c9_--

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

Re: 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: A little SQL help

--_e4b7f6cc-bdbf-4752-a994-7ab36855d555_
Content-Type: text/plain; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable


sorry, went to bed=20
=20
getting a sql error 1064 error in syntax
=20
bastien> Date: Thu, 31 Jan 2008 15:48:02 +1100> From: dmagick@gmail.com> To=
: bastien_k@hotmail.com> 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_e=
nd_date, tour.event_name,CASE WHEN result is NULLTHEN 'N/A'ELSE angler_resu=
lts.resultEND CASE > > FROM > > tourLEFT OUTER JOIN angler_results > > ON a=
ngler_results.tour_id =3D tour.record_idWHERE angler_results.angler_id =3D1=
> > > > 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 el=
se?> > -- > Postgresql & php tutorials> http://www.designmagick.com/
____________________________________________________________ _____


--_e4b7f6cc-bdbf-4752-a994-7ab36855d555_--

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

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

RE: A little SQL help

--_020bea61-a80d-4aa5-8c2a-bf1557281f24_
Content-Type: text/plain; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable


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 reco=
rds 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
=20
Bastien> Date: Thu, 31 Jan 2008 10:16:23 +0100> From: evert.lammerts@gmail.=
com> To: bastien_k@hotmail.com> CC: php-db@lists.php.net> Subject: Re: [PHP=
-DB] A little SQL help> > Not completely sure what type of result you expec=
t, but here's one that makes sense to me.> > SELECT tour.record_id, tour.ev=
ent_start_date, tour.event_end_date, tour.event_name, angler_results.result=
> FROM tour> LEFT JOIN angler_results> ON angler_results.tour_id =3D tour.r=
ecord_id> AND angler_results.angler_id =3D 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 u=
sing 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 I=
NNER 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 doi=
ng wrong> > > > SELECT > > DISTINCT (tour.record_id), tour.event_start_date=
, tour.event_end_date, tour.event_name,CASE WHEN result is NULLTHEN 'N/A'EL=
SE angler_results.resultEND CASE > > FROM > > tourLEFT OUTER JOIN angler_re=
sults > > ON angler_results.tour_id =3D tour.record_idWHERE angler_results.=
angler_id =3D1> > > > 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, r=
esult)> > > > > > Any ideas?> > > > Bastien> > > > > > > > ________________=
_________________________________________________> >> >> > >=20
____________________________________________________________ _____


--_020bea61-a80d-4aa5-8c2a-bf1557281f24_--

RE: A little SQL help

--_141d3d8c-7c7d-40c3-bef7-b10633682a06_
Content-Type: text/plain; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable


After some playing and a little sleep
=20
This is the corrected query
=20
SELECT tour.event_start_date, tour.event_end_date, tour.event_name, CASE W=
HEN result IS NULL THEN 'N/A'ELSE resultEND FROM tourLEFT OUTER JOIN angler=
_results ON angler_results.tour_id =3D tour.record_idWHERE (
angler_results.angler_id =3D1OR angler_ID IS NULL=20
)
=20
the goal was to produce this
=20
event_start_date event_end_date event_name result
NULL NULL Sunshine Show Down N/A=20
NULL NULL Citrus Slam 1=20
NULL NULL Lone Star Shootout N/A=20
NULL NULL Battle on the Border N/A=20
=20
=20
Thanks all,
=20
Bastien
> Date: Thu, 31 Jan 2008 09:40:38 -0500> From: peter@westergaard.ca> To: ba=
stien_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 e=
xpecting, > what version of SQL you're using, and where your error is occur=
ring. > > 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 qu=
ery 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 Al=
l,> > > > 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.eve=
nt_start_date, tour.event_end_date, tour.event_name,CASE WHEN result is NUL=
LTHEN 'N/A'ELSE angler_results.resultEND CASE > > FROM > > tourLEFT OUTER J=
OIN angler_results > > ON angler_results.tour_id =3D tour.record_idWHERE an=
gler_results.angler_id =3D1> > > > 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> > > > >=20
____________________________________________________________ _____


--_141d3d8c-7c7d-40c3-bef7-b10633682a06_--