| re: Strange behavier in execution of this SQL query....
On Mon, 11 Jul 2005 00:22:34 +0200, "Geir" <geir6912@hotmail.com>
wrote:
[color=blue]
>Hi,
>
>I'm making a SQL query that shall return which players that have made
>soccergoals in most games in a row in the same season.
>
>Tables:
>KampFakta(kampid(PK),sesong, runde)
>KampMaal((kampid,maalnr)(PK), persid)
>
>
>The Sql query looks like this:
>
>SELECT s1.persid, s1.sesong, s1.runde AS forste, s2.runde AS siste,
>(s2.runde - s1.runde + 1) AS antall
>FROM
>(Select sesong,runde,persid from KampFakta,KampMaal where sesong=2003 and
>KampFakta.kampid=KampMaal.kampid) AS s1,
>(Select sesong,runde,persid from KampFakta,KampMaal where sesong=2003 and
>KampFakta.kampid=KampMaal.kampid) AS s2
>WHERE s2.sesong = s1.sesong
>AND s2.persid = s1.persid
>AND s2.runde > s1.runde
>AND not exists (SELECT runde
> FROM KampFakta AS k
> WHERE k.sesong = s1.sesong
> AND k.runde > s1.runde
> AND k.runde < s2.runde
> AND not exists (SELECT *
> FROM KampMaal
> WHERE kampid =
>k.kampid
> AND persid =
>s1.persid));
>
>
>This SQL only returns players that have scored in 2 games in a row. While i
>have players registered with at least 4 games in a row with goals.
>
>When i run the same SQL query in Postgresql i get the right result.
>
>It's something to do with the "NOT EXISTS" blocks. If i leave out that part
>it all looks good at that stage in the query.
>
>Any ideas why this query clearly is interpreted different in MS-Access /
>Postgresql ?
>Or why it does not work at all ?
>
>
>Regards,
>Geir
>Norway
>
>[/color]
Hi, how is the weather in Norway?
I can't answer your question as such but as no-one else has answered
yet maybe this is useful - it avoids using subqueries.
If you declared a table mynumbers (mynumber(PK)) of integers from 1 to
100 (say - the max number of matches in a season)
and you have a table
goals(player, matchnumber, season, goals)
with the first three fields the PK - this is the equivalent of your
table
and for convenience a query "scored" =
SELECT player, matchnumber, season FROM goals WHERE goals>0
then the query
SELECT season, player, mynumbers_1.mynum AS runlength, mynumbers.mynum
AS startmatch
FROM scored, mynumbers, mynumbers AS mynumbers_1
WHERE scored.matchnumber Between [mynumbers].[mynum] And
[mynumbers].[mynum]+[mynumbers_1].[mynum]-1
GROUP BY scored.season, scored.player, mynumbers_1.mynum,
mynumbers.mynum
HAVING mynumbers_1.mynum>1 AND
Count(scored.matchnumber)=[mynumbers_1].[mynum]
should give you you all the runs of consecutive scoring matches by
startmatch and player and runlength.
However, test it a bit!
HTH
David
| 
