I would like to get tokens
based on one WHERE
condition. E.g if i would like to SELECT
Gregor
and if we look into committee
it should retrive then the token for Liza, Matt
Table structure:
CREATE TABLE users(
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(255)
);
INSERT INTO users (name)
VALUES ("Gregor"),
("Liza"),
("Matt"),
("Bob");
CREATE TABLE committee(
user_id INT,
friend_id INT,
member_id INT,
PRIMARY KEY (`user_id`, `friend_id`, `member_id`),
FOREIGN KEY (`user_id`) REFERENCES `users` (`id`),
FOREIGN KEY (`friend_id`) REFERENCES `users` (`id`),
FOREIGN KEY (`member_id`) REFERENCES `users` (`id`)
);
INSERT INTO committee (user_id, friend_id, member_id)
VALUES (1, 2, 3),
(1, 2, 4);
CREATE TABLE tokens(
user_id INT,
token VARCHAR(255)
);
INSERT INTO tokens (user_id, token)
VALUES (1, "lclasld2"),
(2, "xxkakdasd"),
(3, "123kdkfs"),
(4, "hjj32");
Current query i got:
SELECT token FROM tokens WHERE user_id = 1;
How it behave now:
user_id, token
1, lclasld2
What i expect, when i run the query:
user_id, token
2, xxkakdasd
3, 123kdkfs
4, hjj32
CodePudding user response:
You can union the select friend _id and member_id for user_id 1
SELECT user_id, token FROM tokens WHERE user_id IN (SELECT friend_id FROM committee WHERE user_id = 1 UNION SELECT member_id FROM committee WHERE user_id = 1)
user_id | token ------: | :-------- 2 | xxkakdasd 3 | 123kdkfs 4 | hjj32
db<>fiddle here
CodePudding user response:
Instead of trying to insert all of values in one line, trying adding an insert for each individual object like so: INSERT INTO TOKENS() VALUES(); INSERT INTO TOKENS() VALUES()
, so on and so forth.