Hi, I have the following tables btu I can;t figure out the 'correct' thing to query for. Here are the tables table: battles battle_id | challenger_id | defender_id | challenger_win | defender_win | 1 | 25 | 32 | 1 | 0 | 2 | 32 | 25 | 1 | 0 | 3 | 9 | 25 | 0 | 1 | Code (markup): I need to display the players by how many wins they have. However, a player can either be a challenger OR defender. and the challenger_win, defender_win is either a 1 or 0, and tells who got a win from the match. I also need to reference to the players table to get the correct name for the player. I've been trying: "SELECT battles.challenger_id AS challenger_id, battles.defender_id AS defender_id, battles.battle_id AS battle_id, (SUM(battles.challenger_win) + SUM(battles.defender_win)) AS wins FROM battles GROUP BY battles.challenger_id, battles.defender_id ORDER BY wins DESC "; PHP: To get the basic structure correct first (I should be ok getting the name from the other table after) but it's displaying a seperate row for the defender and challengers. I would appreciate a bit of help with this Cheers!
Can you alter the tables. It would be much better if you remove the "challenger_win" and "defender_win" fields and replaced them with something to the effect of "winner". Failing that, you could just add the "winner field and only use it for this purpose. Then just enter the id of the winner in that field and it will be much easier to calculate how many wins and losses a player has. I have some experience with this sort of thing from a couple of hockey league related sites I have built scripts for. Please don't hesitate to ask if you need more help with this.
Hi Dustin, After making this post, I went away and made this change `winner` now holds the ID of the winning player. I need to list them in desc/asc order by wins, but I still can't get the correct query! Should I be looking at a self join?