MYSQL column selection conditionally

Question

An example schema for my question is given in SQLFiddle and as follows:

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 an online game, I want to choose an adversary, which may be player1id or player2id in the player.

Example Required I / O

 Input 1: Output 5, 7, 2 Input 5: Output 1, 3 ,4 & so on. 

That is, the required data can be in any column, but I need output in one column conditionally or in any other way. I heard about a conditional MySQL column, but could not create a query to get the required output. Someone can help with the request.

Edit

Based on this link , I run the following query, but failed.

 SELECT IF(PLAYER1ID IS 1,PLAYER2ID as opponent,PLAYER1ID as opponent) FROM players WHERE PLAYER1ID = 1 OR PLAYER2ID = 1; 

Answer 1

I think you can use case when then syntax as below:

 SELECT CASE WHEN player1id = 1 THEN player2id ELSE player1id END FROM players WHERE player1id =1 OR player2id=1; 

As an alternative:

 SELECT CASE WHEN player1id = 1 THEN player2id WHEN player2id =1 THEN player1id END FROM players WHERE player1id =1 OR player2id=1; 

Answer 2

 select player1id from players where player2id=1 union select player2id from players where player1id=1 

SQL FIDDLE DEMO

Answer 3

This should give you what you are looking for:

 SET @PlayerId = 1; SELECT `player2id` AS 'Other Player ID' FROM `players` WHERE `player1id` = @PlayerId AND `player2id` != @PlayerId UNION SELECT `player1id` FROM `players` WHERE `player2id` = @PlayerId AND `player1id` != @PlayerId; SET @PlayerId = 5; SELECT `player2id` AS 'Other Player ID' FROM `players` WHERE `player1id` = @PlayerId AND `player2id` != @PlayerId UNION SELECT `player1id` FROM `players` WHERE `player2id` = @PlayerId AND `player1id` != @PlayerId;