title: SQL with JOIN returning all the combinations instead of the respective matches
body: <p>I have 3 tables, matches, players and decks.</p>
  
  <p>I want to select from the table matches results and attach the respective name from players and decks based on their id.</p>
  
  <p>I've tried with JOIN and WHERE with no results, see fiddle here: </p>
  
  <p><a href="https://www.db-fiddle.com/f/pPmpqxaUbA6dGFT93Fa1AV/3" rel="nofollow noreferrer">https://www.db-fiddle.com/f/pPmpqxaUbA6dGFT93Fa1AV/3</a></p>
  
  <p>The <strong>problem</strong> with the join is that it is <strong>showing all the combinations</strong> instead of only the one that matches.</p>
  
  <pre><code>    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');
  </code></pre>
  
  <p>And what i've tried so far with no success</p>
  
  <pre><code>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;
  </code></pre>