Problems with SQL Query

[ralphiooo]

Hi, i have the following table structure:

players:
- player_id (primary key)
- player_name

player_points:
- player_id
- points
- date_created (date record added)

The players to player_points tables have a 1 to many relationship. Therefore there can be multiple records in the player_points table with the same player_id. What i need to do is a query that gets all the players and their latest points score (based on the date_created)

So far i have:

SELECT
`player_points`.`points`,
`players`.`player_name`
FROM
`player_points`
Left Outer Join `players` ON `player_points`.`player_id` = `players`.`player_id`
ORDER BY
`player_points`.`date_created` DESC

but it returns every record from the player_points table.

Appreciate if someone could show me where i'm going wrong.

Thanks

 

[guelphdad]

You want a group by clause:

SELECT
players`.`player_id
, `players`.`player_name`
, sum(`player_points`.`points`) as total,
FROM
`player_points`
Left Outer Join `players` ON `player_points`.`player_id` = `players`.`player_id`
group by
players`.`player_id
ORDER BY
total DESC

Note you don't need the backticks in a column reference or table reference unless you are using a word that is on the reserved words list. you can include them if it is easier for you to keep track.

 

[ralphiooo]

Hi cheers, i adapted your solution and came up with:

SELECT
`player_points`.`points`,
`players`.`player_id`,
`players`.`player_name`
FROM
`player_points`
Left Outer Join `players` ON `player_points`.`player_id` = `players`.`player_id`
GROUP BY
`player_points`.`player_id`
ORDER BY
`player_points`.`date_created` DESC,
`teams`.`team_name` ASC,
`players`.`player_name` ASC

I still can't quite see how it works because in mssql i'm sure you have to add a group by for every column you select from the player_points table and as no group by is on the points i can't see how it only returns the 1 (latest added) row instead of all of them. This is what i thought my original problem was but i guess the solution was simpler than i thought.

Thanks again.

 

[guelphdad]

what is it that you are trying to do? Are you not trying to total all the points for each player?

Your intuition about the group by clause is also correct. In MSSQL you would get a failure on the query. MySQL allows you to group by hidden fields, but that could also result in incorrect data. You need each item in the select clause to be in the group by clause.

My query totals the points for each player. Your query does not do that as nowhere do you have a sum.

 

[ralphiooo]

Hi on further testing i am now starting to get some funny results appear. I don't wish to total the points, i wish to join the players table with the latest added record in the player_points table with the same player_id's and then retrieve the player_name and corresponding points. Hope that makes it clearer.

 

[guelphdad]

Can you show some sample data and then from that also show your expected output? I'm not exactly sure what it is you want to show.

Do you only want to show the latest points for each player?

Perhaps you are recording home runs for baseball players and only want to show them from 2006 for each player but not a lifetime total?

 

[r937]

select P.player_name
     , PP.points
     , PP.date_created
  from players as P
left outer 
  join player_points as PP
    on PP.player_id = P.player_id
   and PP.date_created
     = ( select max(date_created)
           from player_points
          where player_id = P.player_id )

r937.com | rudy.ca

 

[guelphdad]

I CAN'T READ!!! You even said that in the first post.

 

[ralphiooo]

Hehe, people tend to make that mistake . Cheers r937 the solution worked a treat.

Thanks for your help everyone.