MySQL Forums
Forum List  »  Newbie

Query challengers table
Posted by: Jassim Rahma
Date: June 10, 2022 01:56PM

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

Options: ReplyQuote


Subject
Written By
Posted
Query challengers table
June 10, 2022 01:56PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.