Mysql How to SELECT information based other tables-CodePudding

I would like to get tokens. E.g if i would like to SELECT Gregor, Liza and if we look into committee it should retrive then their own tokens and also Matt, Bob tokens

Table structure:

CREATE TABLE users(
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255)
);

INSERT INTO users (name)
VALUES ("Gregor"),
    ("Liza"),
    ("Matt"),
    ("Bob"),
    ("Tom");
    
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"),
    (5, "zz3dja");

Current query i got:

SELECT token FROM tokens WHERE user_id IN (1, 2);

How it behave now:

user_id, token
1, lclasld2
2, xxkakdasd

What i expect, when i run the query:

user_id, token
1, lclasld2
2, xxkakdasd
3, 123kdkfs
4, hjj32

CodePudding user response：

I think it's enough to use the query without where clause

SELECT * FROM tokens

CodePudding user response：

Your database design could be better or the schema values that you have used for demo arent correct.

You can try this

SELECT token
FROM   tokens
WHERE  user_id IN (SELECT member_id
                   FROM   committee
                   WHERE  user_id IN ( 1, 2 ))
        OR user_id IN ( 1, 2 );