All we need is an easy explanation of the problem, so here it is.
I’ve not used SQL in quite a while and I’m pretty rusty it seems. I’ve had to do a project recently and needed to use MySQL on Windows and when looking for solutions to this particular query, I get told that you can use COUNT(*) implicitly somehow, but I can’t get it to work right.
What I need is something like:
I have made a simple SQL fiddle of my database here.
Could someone help me out with this? I’m willing to accept that I might have to make a more involved solution with some
LOOP but thought I’d ask here. It’s my first question on this community, so sorry if I didn’t format this right or it wasn’t an appropriate question.
How to solve :
I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.
This select first all individual player_IDs and uses them to get the numbers
CREATE TABLE `game_match` ( `ID` INT(10) NOT NULL AUTO_INCREMENT COMMENT 'The unique ID of the match,', `ACCOUNT_ONE_ID` INT(10) NOT NULL COMMENT 'Foreign key for Player 1.', `ACCOUNT_TWO_ID` INT(10) NOT NULL COMMENT 'Foreign key for Player 2.', `WINNER_ID` INT(10) NOT NULL COMMENT 'Foreign key for which of the two players won.', INDEX(ACCOUNT_ONE_ID, ACCOUNT_TWO_ID, WINNER_ID) , PRIMARY KEY (`ID`) USING BTREE ); INSERT INTO game_match (ACCOUNT_ONE_ID, ACCOUNT_TWO_ID, WINNER_ID) VALUES (0, 1, 0), (1, 2, 2), (1, 2, 1), (0, 1, 0), (1, 0, 0), (2, 1, 2), (0, 2, 0), (0, 1, 1);
SELECT DISTINCT PlayerID, (SELECT COUNT(*) FROM game_match WHERE (ACCOUNT_ONE_ID = t1.PlayerID OR ACCOUNT_TWO_ID = t1.PlayerID) AND WINNER_ID = t1.PlayerID) MATCHES_WON, (SELECT COUNT(*) FROM game_match WHERE (ACCOUNT_ONE_ID = t1.PlayerID OR ACCOUNT_TWO_ID = t1.PlayerID) AND WINNER_ID <> t1.PlayerID) MATCHES_LOST FROM (SELECT DISTINCT ACCOUNT_ONE_ID AS PlayerID FROM game_match UNION SELECT DISTINCT ACCOUNT_TWO_ID FROM game_match) t1
PlayerID | MATCHES_WON | MATCHES_LOST -------: | ----------: | -----------: 0 | 4 | 1 1 | 2 | 5 2 | 2 | 2
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂