Query challengers table

[Jassim Rahma]

Hi,

I have the following table:


CREATE TABLE `challengers` (
`challenger_user_id` bigint(20) NOT NULL AUTO_INCREMENT,
`challenger_id` bigint(20) DEFAULT NULL,
`user_id` bigint(20) DEFAULT NULL,
PRIMARY KEY (`challenger_user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4;

and my data looks like this:

INSERT INTO `challengers` VALUES (1, 1001, 1103);
INSERT INTO `challengers` VALUES (2, 1001, 1102);
INSERT INTO `challengers` VALUES (3, 1100, 1001);
INSERT INTO `challengers` VALUES (4, 1082, 1051);
INSERT INTO `challengers` VALUES (5, 1049, 1045);
INSERT INTO `challengers` VALUES (6, 1042, 1001);


Now I want if the user logged in in my system is user_id : 1001 then I want to get a list of users linked to him in the challengers table:

SELECT user_id, first_name, last_name
FROM challengers
JOIN oneid.users challenger ON challenger.user_id = challengers.challenger_id
JOIN oneid.users the_user ON the_user.user_id = challengers.challenger_id
WHERE challengers.challenger_id = 1001
OR challengers.user_id = 1001;


but the user_id, first_name, last_name is for the other user not for the 1001 so for example in the first record I want to get it for user 1103 and for second record I want it for 1102 and for the third record I want it for 1100