0

I have 3 tables, matches, players and decks.

I want to select from the table matches results and attach the respective name from players and decks based on their id.

I've tried with JOIN and WHERE with no results, see fiddle here:

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

The problem with the join is that it is showing all the combinations instead of only the one that matches.

    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');

And what i've tried so far with no success

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;
Evandro Teixeira
  • 321
  • 3
  • 18
José Moreira
  • 157
  • 9

3 Answers3

1

Try:

    SELECT * FROM `matches`
    LEFT JOIN players players1
        ON matches.pid_1 = players1.pid 
    LEFT JOIN players players2
        ON matches.pid_2 = players2.pid 
    LEFT JOIN decks deck1
        ON matches.did_1 = deck1.did 
    LEFT JOIN decks  deck2
        ON matches.did_2 = deck2.did 
    WHERE matches.tid = 2;
Savo Zirojević
  • 141
  • 5
  • LEFT JOIN means all record from left table even in a case when the right table does not have a record that matches criteria. In your case, we want all matches, even in a case that no players. If we use the RIGHT JOIN we will get all registered players and in some case null as a match if that player doesn't play any match. – Savo Zirojević Dec 06 '18 at 11:54
1

You can try like below by using 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

Zaynul Abadin Tuhin
  • 31,407
  • 5
  • 33
  • 63
1

Instead of just JOIN, you should try more restrictive outputs like INNER JOIN (which is the one I think you need) or LEFT/RIGHT JOIN.

You may check all the difference between JOINs at this Q/A: Difference between joins

John
  • 165
  • 1
  • 8
  • i thought i would need the right join in this case, but i need to refresh my understading on sql, will read that later, thanks. – José Moreira Dec 06 '18 at 12:42