Thread: Why doesn't this query work? Puzzle to hard for me to crack

This query works as I want it to:

SELECT sum(cutoffuohands) AS HANDS, sum(cutoffuopfr) AS pfr
FROM compiledplayerresultspositionaction
JOIN compiledresults
ON compiledplayerresultspositionaction.compiledplayer results_id = compiledresults.compiledplayerresults_id
JOIN players
ON compiledresults.player_id = players.player_id
WHERE playername = 'Robert'

It returns the number of "cutoffuohands" and the number of "cutoffuopfr" for the player Robert.

--------------------------------------------------------

But this similar query does not work as I want it to do:

SELECT sum(cutoffuohands) AS HANDS, sum(cutoffuopfr) AS pfr
FROM compiledplayerresultspositionaction
JOIN compiledresults
ON compiledplayerresultspositionaction.compiledplayer results_id = compiledresults.compiledplayerresults_id
JOIN players
ON compiledresults.player_id = players.player_id
WHERE playername = 'Robert'


I want it to return all the "cutoffuohands" and "cutoffuopfr" for all players not named Robert. But it returns unreasonable numbers.

Unfortunately I don't know much about the inner workings of the database and unfortunately I'm only a novice with SQL so I haven't been able to figure out how to fix the second query. I hope you guys here on the mailing list can take a lot at this and see if you can figure it out.

Thanks!

These queries are identical!

I'm guessing your query works because there is only one player named
Robert.  If there were more than one Robert; the query will likely also
produce incorrect results.

IAC, try adding "GROUP BY players.player_id".




sdger erger wrote:
> This query works as I want it to:
>
> SELECT sum(cutoffuohands) AS HANDS, sum(cutoffuopfr) AS pfr
> FROM compiledplayerresultspositionaction
> JOIN compiledresults
> ON compiledplayerresultspositionaction.compiledplayer results_id =
> compiledresults.compiledplayerresults_id
> JOIN players
> ON compiledresults.player_id = players.player_id
> WHERE playername = 'Robert'
>
> It returns the number of "cutoffuohands" and the number of "cutoffuopfr"
> for the player Robert.
>
> --------------------------------------------------------
>
> But this similar query does not work as I want it to do:
>
> SELECT sum(cutoffuohands) AS HANDS, sum(cutoffuopfr) AS pfr
> FROM compiledplayerresultspositionaction
> JOIN compiledresults
> ON compiledplayerresultspositionaction.compiledplayer results_id =
> compiledresults.compiledplayerresults_id
> JOIN players
> ON compiledresults.player_id = players.player_id
> WHERE playername = 'Robert'
>
>
> I want it to return all the "cutoffuohands" and "cutoffuopfr" for all
> players not named Robert. But it returns unreasonable numbers.
>
> Unfortunately I don't know much about the inner workings of the database
> and unfortunately I'm only a novice with SQL so I haven't been able to
> figure out how to fix the second query. I hope you guys here on the
> mailing list can take a lot at this and see if you can figure it out.
>
> Thanks!