CODEWITHSUNDEEP
mysql
Printer
Printer

Reputation: 465

Mysql SELECT multiple users key if id exsist in the row

I would like to fetch all users.myKey from the table committee if the id is in the table.

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

INSERT INTO users (name, myKey)
VALUES ("Gregor", "kx4ht"),
    ("Liza", "1lPxk"),
    ("Matt", "mP3fd"),
    ("Bob", "zStr5");
    
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 (4, 1, 3),
    (1, 2, 3);

What i got now:

SELECT u.myKey FROM users u INNER JOIN committee c ON (c.user_id = u.id || c.friend_id = u.id || c.member_id = u.id) WHERE u.id = 2 GROUP BY u.id;

Result now: I get only my own myKey user_id2

What i expect: I want only get the myKeys for the others inside committee. E.g if i user 2 want get the myKeys from table committee where i can find my user id 2

In this case it should return the myKey for user1 and user3

Upvotes: 1

Views: 55

Answers (1)

Giusè Reichardt
Giusè Reichardt

Reputation: 66

As far as I understand your problem, you want to find a specific id in the "committee" table, find the id's that are next to the specified id and then find those neighboring ids in the "users" table and show their keys.

This is what I have come up with:

SELECT u.id, u.myKey
    FROM users u
    LEFT JOIN (SELECT IF (tmp.user_id = 2, NULL, tmp.user_id) AS user_id,
                IF(tmp.friend_id = 2, NULL, tmp.friend_id) AS friend_id,
                IF(tmp.member_id = 2, NULL, tmp.member_id) AS member_id
            FROM (SELECT *
                    FROM committee
                    WHERE user_id = 2 OR friend_id = 2 OR member_id = 2) AS tmp) AS id_table
    ON u.id = id_table.user_id OR u.id = id_table.friend_id OR u.id = id_table.member_id
    WHERE user_id IS NOT NULL OR friend_id IS NOT NULL OR member_id IS NOT NULL;

Note that I am searching for the user with the id 2, as you specified.

The result of this query, as you said you would have expected it:

id | myKey
1 kx4ht
3 mP3fd

Upvotes: 2

Related Questions