英文:
Left Join with multiple tables: not always expected result
问题
下面的查询用于搜索特定玩家的旧游戏以及两个玩家的等级变化。等级变化存储在名为"ratingverloop"的单独表中，因为它们需要用于等级变化的趋势，而太旧的游戏将在一定时间后被删除。很多情况下，等级变化的数据是不正确的。
在屏幕上显示结果集时，显示了并非总是使用whitePlayer和blackPlayer的playerID。有时会两次使用相同的playerID。
在MySQL中检查数据，发现了我的问题：对于相同的gameID，等级变化至少存储两次。为每个玩家各自存储一行（用于显示等级变化图表中的时间）。有时，在游戏的修正/重置情况下，会存储更多。
我两次加入了"ratingverloop"表，每次都是为一个玩家（rv1和rv2）。
但我如何确保获取正确的行呢？我只是找不到任何类似的示例来使用左连接（是否应该在这里使用它，或是否有其他解决方案？）
$sql = "SELECT games.gameID
,whitePlayer
,blackPlayer
,gameMessage
,messageFrom
,date_format(dateCreated , '%d-%m-%Y') AS startdatum
,lastMove
,date_format(lastMove , '%d-%m-%Y') AS lastMovex
,whiteNick
,blackNick
,rv1.userID AS userID1
,rv1.ratingvan AS userID1_ratingvan
,rv1.rating AS userID1_rating
,rv2.userID AS userID2
,rv2.ratingvan AS userID2_ratingvan
,rv2.rating AS userID2_rating
FROM games
LEFT JOIN ratingverloop rv1 ON games.gameID = rv1.gameID
LEFT JOIN ratingverloop rv2 ON games.gameID = rv2.gameID
WHERE
(
gameMessage <> ''
AND gameMessage <> 'playerInvited'
AND gameMessage <> 'inviteDeclined'
)
AND
(
whitePlayer = :playerID
OR blackPlayer = :playerID
)
ORDER BY lastMove DESC;";
结果userID1和userID2来自相同的行。
它应该是：2187和7144。
英文:
The query below is ment for searching old games from specific player and ratingchanges from both players. Ratingchanges are stored in own table called "ratingverloop", because they need to be saved for trending of ratingchanges, while too old games will be deleted after a certain time. A lot of times, data of ratingchanges are not correct.
Displaying the resultset to screen, showed that not always playerIDs of whitePlayer and blackPlayer were used. Sometimes same was used twice.
Checking data in MySQL itself, revealed my problem: Fore the same gameID, ratingchanges are stored at least twice. For both players, theire own row. (used for showing graphic of ratingchanges in time). And sometimes more in case of a corrected/reset of a game.
I have joined table ratingverloop twice, each for one player. (rv1,rv2).
But how can I be sure to get the right row? I just cannot find any simular examples om how use a left join for this. (should I use it here anyways, or is there another solution?)
$sql = "SELECT games.gameID
,whitePlayer
,blackPlayer
,gameMessage
,messageFrom
,date_format(dateCreated , '%d-%m-%Y') AS startdatum
,lastMove
,date_format(lastMove , '%d-%m-%Y') AS lastMovex
,whiteNick
,blackNick
,rv1.userID AS userID1
,rv1.ratingvan AS userID1_ratingvan
,rv1.rating AS userID1_rating
,rv2.userID AS userID2
,rv2.ratingvan AS userID2_ratingvan
,rv2.rating AS userID2_rating
FROM games
LEFT JOIN ratingverloop rv1 ON games.gameID = rv1.gameID
LEFT JOIN ratingverloop rv2 ON games.gameID = rv2.gameID
WHERE
(
gameMessage <> ''
AND gameMessage <> 'playerInvited'
AND gameMessage <> 'inviteDeclined'
)
AND
(
whitePlayer = :playerID
OR blackPlayer = :playerID
)
ORDER BY lastMove DESC";
[EDIT]As asked:
CREATE TABLE `ratingverloop` (
`index` int(11) NOT NULL,
`userID` int(10) UNSIGNED NOT NULL,
`datumtijd` datetime NOT NULL,
`gameID` int(11) NOT NULL,
`ratingvan` smallint(5) UNSIGNED NOT NULL,
`rating` smallint(5) UNSIGNED NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
--
-- Gegevens worden geëxporteerd voor tabel `ratingverloop`
--
INSERT INTO `ratingverloop` (`index`, `userID`, `datumtijd`, `gameID`, `ratingvan`, `rating`) VALUES
(3071747, 7144, '2023-03-03 00:46:40', 2786547, 1193, 1198),
(3071748, 2187, '2023-03-03 00:46:40', 2786547, 938, 933);
--
-- Indexen voor geëxporteerde tabellen
--
--
-- Indexen voor tabel `ratingverloop`
--
ALTER TABLE `ratingverloop`
ADD PRIMARY KEY (`index`),
ADD KEY `userID` (`userID`),
ADD KEY `gameID` (`gameID`);
Result:
(
[gameID] => 2786547
[whitePlayer] => 2187
[blackPlayer] => 7144
[gameMessage] => checkMate
[messageFrom] => black
[startdatum] => 23-01-2023
[lastMove] => 2023-03-03 00:46:40
[lastMovex] => 03-03-2023
[whiteNick] => @Gijs
[blackNick] => E-Street
[userID1] => 7144
[userID1_ratingvan] => 1193
[userID1_rating] => 1198
[userID2] => 7144
[userID2_ratingvan] => 1193
[userID2_rating] => 1198
)
Result userID1 and userID2 are from the same row.
It should be: 2187 and 7144
--
-- Tabelstructuur voor tabel `games`
--
CREATE TABLE `games` (
`gameID` int(20) UNSIGNED NOT NULL,
`whitePlayer` mediumint(9) DEFAULT NULL,
`blackPlayer` mediumint(9) DEFAULT NULL,
`gameMessage` enum('','playerInvited','inviteDeclined','draw','playerResigned','checkMate','VERLOPEN','ONGELDIG','tijdoverschrijding','ingetrokken','stalemate','zettijdoverschrijding') NOT NULL,
`messageFrom` enum('','black','white','Admin') NOT NULL,
`wit_rem_aanvraag` datetime NOT NULL,
`zwart_rem_aanvraag` datetime NOT NULL,
`rem_antw_zwart` datetime NOT NULL,
`rem_antw_wit` datetime NOT NULL,
`dateCreated` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`lastMove` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`ratingset` enum('notset','set') NOT NULL DEFAULT 'notset',
`rating_white_start` int(11) DEFAULT NULL,
`rating_white_end` int(11) NOT NULL DEFAULT '0',
`rating_black_start` int(11) DEFAULT NULL,
`rating_black_end` int(11) NOT NULL DEFAULT '0',
`notitie_wit` text NOT NULL,
`notitie_zwart` text NOT NULL,
`vak_wit` date NOT NULL DEFAULT '0000-00-00',
`vak_zwart` date NOT NULL DEFAULT '0000-00-00',
`her_invite` tinyint(4) NOT NULL DEFAULT '0',
`pionpromo` tinyint(4) NOT NULL DEFAULT '0',
`beurtaan` tinytext NOT NULL,
`whiteNick` tinytext,
`blackNick` tinytext,
`kibW` enum('ja','nee') NOT NULL DEFAULT 'ja',
`kibZ` enum('ja','nee') NOT NULL DEFAULT 'ja',
`comment` tinytext NOT NULL,
`witOnline` datetime NOT NULL,
`zwartOnline` datetime NOT NULL,
`chataanvraagdoor` int(10) UNSIGNED NOT NULL,
`chataanvraagtijd` datetime NOT NULL DEFAULT '1900-00-00 00:00:00',
`ftoernooi` enum('0','1') NOT NULL DEFAULT '0',
`tafel` enum('0','1') NOT NULL DEFAULT '0',
`ratingrange` smallint(5) UNSIGNED NOT NULL DEFAULT '500',
`speeltijd` mediumint(3) UNSIGNED DEFAULT NULL,
`speeltijdwit` mediumint(8) UNSIGNED NOT NULL,
`speeltijdzwart` mediumint(8) UNSIGNED NOT NULL,
`maxzettijd` time NOT NULL DEFAULT '168:00:00',
`startklok` datetime NOT NULL,
`opener` mediumint(8) UNSIGNED DEFAULT NULL,
`ts_ladder` tinyint(3) UNSIGNED DEFAULT NULL COMMENT 'teamschaken laddertoernooi',
`t_partij` tinyint(3) UNSIGNED NOT NULL DEFAULT '0',
`tt` tinyint(3) UNSIGNED NOT NULL COMMENT 'teamtoernooi',
`rated` tinyint(3) UNSIGNED NOT NULL DEFAULT '1',
`klok` tinyint(4) NOT NULL DEFAULT '0',
`toernooisoort` tinyint(10) UNSIGNED NOT NULL DEFAULT '0' COMMENT '0 = geen toernooi, 1 = tsreg',
`vakmodus` tinyint(4) NOT NULL DEFAULT '0',
`50zetten` tinyint(3) UNSIGNED NOT NULL DEFAULT '0'
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='herinvite: 1=uitnodiging, 2 voor lopende partij';
--
-- Gegevens worden geëxporteerd voor tabel `games`
--
INSERT INTO `games` (`gameID`, `whitePlayer`, `blackPlayer`, `gameMessage`, `messageFrom`, `wit_rem_aanvraag`, `zwart_rem_aanvraag`, `rem_antw_zwart`, `rem_antw_wit`, `dateCreated`, `lastMove`, `ratingset`, `rating_white_start`, `rating_white_end`, `rating_black_start`, `rating_black_end`, `notitie_wit`, `notitie_zwart`, `vak_wit`, `vak_zwart`, `her_invite`, `pionpromo`, `beurtaan`, `whiteNick`, `blackNick`, `kibW`, `kibZ`, `comment`, `witOnline`, `zwartOnline`, `chataanvraagdoor`, `chataanvraagtijd`, `ftoernooi`, `tafel`, `ratingrange`, `speeltijd`, `speeltijdwit`, `speeltijdzwart`, `maxzettijd`, `startklok`, `opener`, `ts_ladder`, `t_partij`, `tt`, `rated`, `klok`, `toernooisoort`, `vakmodus`, `50zetten`) VALUES
(2786547, 2187, 7144, 'checkMate', 'black', '0000-00-00 00:00:00', '0000-00-00 00:00:00', '0000-00-00 00:00:00', '0000-00-00 00:00:00', '2023-01-23 22:11:26', '2023-03-03 00:46:40', 'set', NULL, 0, NULL, 0, '', '', '0000-00-00', '0000-00-00', 0, 0, '2187', '@Gijs', 'E-Street', 'ja', 'ja', '', '2023-03-02 21:58:22', '2023-03-03 00:46:56', 0, '1900-00-00 00:00:00', '0', '0', 500, NULL, 0, 0, '168:00:00', '2023-01-24 09:06:01', NULL, NULL, 0, 0, 1, 0, 0, 0, 6);
--
-- Indexen voor geëxporteerde tabellen
--
--
-- Indexen voor tabel `games`
--
ALTER TABLE `games`
ADD UNIQUE KEY `gameID_2` (`gameID`),
ADD KEY `gameID` (`gameID`),
ADD KEY `idx_partijlijst_datum` (`gameMessage`,`lastMove`),
ADD KEY `inx_tafel` (`tafel`),
ADD KEY `idx2_whitePlayer` (`whitePlayer`),
ADD KEY `idx2_blackPlayer` (`blackPlayer`),
ADD KEY `toernooisoort` (`toernooisoort`),
ADD KEY `vakmodus` (`vakmodus`),
ADD KEY `expired_games` (`lastMove`,`ratingset`,`vak_wit`,`vak_zwart`,`ts_ladder`,`klok`);
--
-- AUTO_INCREMENT voor geëxporteerde tabellen
--
--
-- AUTO_INCREMENT voor een tabel `games`
--
ALTER TABLE `games`
MODIFY `gameID` int(20) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2795936;
答案1
得分: 1
你需要在连接条件中包括白方和黑方的用户ID：
LEFT JOIN ratingverloop rv1
ON games.gameID = rv1.gameID AND games.whitePlayer = rv1.userID
LEFT JOIN ratingverloop rv2
ON games.gameID = rv2.gameID AND games.blackPlayer = rv2.userID
英文:
You need to include the white and black user IDs in the join conditions:
LEFT JOIN ratingverloop rv1
ON games.gameID = rv1.gameID AND games.whitePlayer = rv1.userID
LEFT JOIN ratingverloop rv2
ON games.gameID = rv2.gameID AND games.blackPlayer = rv2.userID
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论