listing record, by adding records from another table

[Guest_imported]

Hi, I'm not sure how to go about this but I've got 2 tables a players_tbl and a users_tbl. With the fields listed below:

players_tbl
-id
-name
-score

users_tbl
-id
name
-player1 [this is the id of the player from the players_tbl]
-player2
-player3
-player4
-player5
-player6
-player7
-player8
-player9
-player10
-player11

k what I need todo is list all the users arranged by their total score [ this is calculated by adding the score up from the players_tbl where player1 to player11's id = the idea in the players_tbl] and put their total score next to their name.

I know it's quite confusing but i'd be greatful for your help, thanx

 

[AnakinPt]

i don't understand what you pretend, and i don't get the model. Please explain better.
Anikin
Hugo Alexandre Dias
Web-Programmer
anikin_jedi@hotmail.com

 

[KarveR]

Something like this should work:

select p.id,u.name,p.sum(score) from players_tbl p users_tbl u where p.id=u.id order by score;

***************************************
Party on, dudes!

 

[AnakinPt]

nope ... it will not

select p.id,u.name,p.sum(score) from players_tbl p, users_tbl u where p.id=u.id group by p.id,u.name order by score;

don't forget the group by.

but i still don't understand the model Anikin
Hugo Alexandre Dias
Web-Programmer
anikin_jedi@hotmail.com

 

[Guest_imported]

That's nearly it, i need it to say sum score where player1, player 2, player 3, player 4, player 5, player 6, player 7, player 8, player 9, player 10, player 11 all = id.

Cus i'm just learning basic sql commands could someone say what the group by thingy does. Thanx

 

[AnakinPt]

but what do you want to do?

you have a table with all the players and you have a table with all the scores

the scores are againist other player (like chess results) or the result of a round (like in shooting)
?
Anikin
Hugo Alexandre Dias
Web-Programmer
anikin_jedi@hotmail.com

 

[Guest_imported]

k do you know what a football fantasy football thing is, the user signups and selects 11 players they wish to have in their team. Then each player is given a score and the user can view a table which shows their total score and other users total score and arranges it so the person with the highest score is at the top. The total score for each user is calculated by added all 11 players score together]. That's basically it.

Cheers

 

[AnakinPt]

ok

your dbmodel should be something like this:

players
id (PK)
name (player name)

scores
id_player (FK)
score (score in one week)
week (week of the game)

users
id (PK)
name (name of user/team)
id_player1 (FK)
...
id_player11 (FK)

users_scores
id_user (FK)
score
week

With a model like this you can use substitutions.

you can get all the points from a all users (top) like this:
SELECT u.id,u.name,sum(us.score) as score from users_scores us, users u where u.id=us.id_user group by u.id,u.name order by sum(us.score)


The group by is used when you want to aggregate info, for example, to sum all the scores of a player. You must group the data for the player, and then calculate the sum.

Other example,
suppose you want to know the list of the best score for all users
SELECT id_user,max(score) as score from users_scores group by id_user order by score


Anikin
Hugo Alexandre Dias
Web-Programmer
anikin_jedi@hotmail.com