Example schema for my question is given at SQLFiddle and as follow:
CREATE TABLE `players` (
`player1id` int(11) NOT NULL,
`player2id` int(11) NOT NULL,
PRIMARY KEY (`player1id`,`player2id`)
) ENGINE=InnoDB;
INSERT INTO `players` values
(1,5),
(1,7),
(5,3),
(5,4),
(2,1);
In a online game, I want to select opponent, which could be in player player1id
or player2id
.
Example Input/ output needed
Input 1: Output 5, 7, 2
Input 5: Output 1, 3 ,4
& so on.
That is, required data could be in any column but I need output in single column conditionally or any other way. I heard about MySQL conditional column but could not create query to get required output. Can someone please help with required query.
Edit
Based on this link, I run following query but failed.
SELECT IF(PLAYER1ID IS 1,PLAYER2ID as opponent,PLAYER1ID as opponent)
FROM players
WHERE PLAYER1ID = 1 OR PLAYER2ID = 1;
This ought to give you what you're looking for:
SQL FIDDLE DEMO
I think you may use
case when then
syntax as below:Alternatively: