dimanche 19 avril 2015

sql selecting and joining rows multiple times

I have a problem with my query selecting rows multiple times, and my left join joining rows multiple times.


The table I am querying looks something like this. The table is partitioned by wins.(this is example data)



+-----------+------------+------+---------+------+--+
| Player_ID | name | wins | matches | nrow | |
+-----------+------------+------+---------+------+--+
| 4070 | Twilight | 1L | 1L | 5L | |
| 4073 | Pinkie Pie | 1L | 1L | 3L | |
| 4071 | Fluttershy | 1L | 1L | 4L | |
| 4077 | jim | 1L | 1L | 2L | |
| 4075 | mike | 1L | 1L | 1L | |
| 4076 | sam | 0L | 1L | 2L | |
| 4072 | Applejack | 0L | 1L | 1L | |
| 4074 | gav | 0L | 1L | 3L | |
+-----------+------------+------+---------+------+--+


and the query looks like this



SELECT player_standings.player_ID, matched_player.player_ID, player_standings.wins, player_standings.nrow, matched_player.nrow
FROM player_standings LEFT JOIN (
SELECT player_ID, wins, nrow FROM player_standings
) AS matched_player ON
(matched_player.wins = player_standings.wins and matched_player.nrow % 2 = 0)
WHERE player_standings.nrow % 2 = 1;


matched players are selected on every second row to avoid getting duplicate values between player1 and player2 columns over all rows . I cannot achieve the same thing by using inequality operators.


the results I get look like this



+---------+---------+------+--------------+--------------+
| player1 | player2 | wins | player1 nrow | player2 nrow |
+---------+---------+------+--------------+--------------+
| 4070 | 4071 | 1L | 5L | 4L |
| 4075 | 4071 | 1L | 1L | 4L |
| 4073 | 4077 | 1L | 3L | 2L |
| 4074 | 4076 | 0L | 3L | 2L |
| 4075 | 4077 | 1L | 1L | 2L |
| 4073 | 4071 | 1L | 3L | 4L |
| 4072 | 4076 | 0L | 1L | 2L |
| 4070 | 4077 | 1L | 5L | 2L |
+---------+---------+------+--------------+--------------+


notice how there are duplicate values for player 1 and player 2.


In summary, what I want, is for player1 to have a unique value for each row. For player2 I want to have a unique value for each row, or to have the value 'null' if there are no matching rows in the query.


Aucun commentaire:

Enregistrer un commentaire