Strange behavier in execution of this SQL query....

[Geir]

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

[David Schofield]

On Mon, 11 Jul 2005 00:22:34 +0200, "Geir" <ge******@hotmail.com>
wrote:

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

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