mysql - 带有 JOIN 的 SQL 返回所有组合而不是各自的匹配项

Question

我有 3 张 table 、比赛、玩家和牌组。

我想从表格中选择匹配结果，并根据他们的 id 附上玩家和套牌的各自名称。

我试过 JOIN 和 WHERE 没有结果，请看这里的 fiddle :

https://www.db-fiddle.com/f/pPmpqxaUbA6dGFT93Fa1AV/3

连接的问题是它显示所有组合而不是仅显示匹配的组合。

    CREATE TABLE `decks` (
  `did` int(10) UNSIGNED NOT NULL,
  `name` varchar(150) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `decks` (`did`, `name`) VALUES
(275, 'Porto'),
(276, 'Ajax'),
(277, 'Trofense'),
(278, 'Barcelona'),
(279, 'Real Madrid'),
(280, 'Braga');


CREATE TABLE `matches` (
  `mid` int(10) UNSIGNED NOT NULL,
  `tid` int(10) UNSIGNED NOT NULL,
  `did_1` int(10) UNSIGNED NOT NULL,
  `did_2` int(10) UNSIGNED NOT NULL,
  `result` int(1) NOT NULL,
  `pid_1` bigint(20) NOT NULL,
  `pid_2` bigint(20) NOT NULL,
  `valid` int(1) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


INSERT INTO `matches` (`mid`, `tid`, `did_1`, `did_2`, `result`, `pid_1`, `pid_2`, `valid`) VALUES
(78, 2, 275, 276, 2, 35, 36, 0),
(79, 2, 277, 273, 1, 37, 38, 0),
(80, 2, 275, 278, 1, 39, 40, 0),
(81, 2, 279, 280, 1, 41, 42, 0),
(82, 2, 276, 277, 2, 36, 37, 0),
(83, 2, 275, 279, 1, 39, 41, 0),
(84, 2, 277, 275, 2, 37, 39, 0);


CREATE TABLE `players` (
  `pid` bigint(20) NOT NULL,
  `name` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;



INSERT INTO `players` (`pid`, `name`) VALUES
(35, 'alberto garcia'),
(36, 'Carlos Munchen'),
(37, 'benjamin Muller'),
(38, 'Toze Bandido'),
(39, 'Peter Parker'),
(40, 'Bruce Wayne'),
(41, 'Logan'),
(42, 'Tony Stark');

到目前为止我已经尝试过但没有成功

SELECT * FROM `matches`
    JOIN players ON matches.pid_1 = players.pid OR matches.pid_2 = players.pid
    JOIN decks ON matches.did_1 = decks.did OR matches.did_2 = decks.did
WHERE matches.tid = 2;

SELECT matches.*, players.name, decks.name FROM `matches`, players, decks
WHERE matches.tid = 2 AND matches.pid_1 = players.pid AND matches.pid_2 = players.pid AND matches.did_1 = decks.did AND matches.did_2 = decks.did;

SELECT * FROM `matches`
    RIGHT JOIN players ON matches.pid_1 = players.pid AND matches.pid_2 = players.pid
    RIGHT JOIN decks ON matches.did_1 = decks.did AND matches.did_2 = decks.did
WHERE matches.tid = 2;

Answer 1

你可以使用 union 像下面这样尝试

SELECT mid,m.result,p1.name as palyer_name,
d1.name as decks_name FROM `matches` m  
left join players p1 on m.pid_1=p1.pid
left join decks d1 on m.did_1=d1.did
union 
SELECT mid,m.result,p2.name,d1.name FROM `matches` m    
left join players p2 on  m.pid_2=p2.pid
left join decks d1 on m.did_2=d1.did

demo fiddle