Task:
I develop messenger, and I’m trying to find only users who don’t share rooms with user X.
I need this to offer users new chats.
Problem:
I can’t to write right SQL query. I was only able to get users who have no rooms at all, and users who have other roomschats(BUT these users have other chats with user X).
MySql dump(simplified):
--
-- Database: `chat`
--
-- --------------------------------------------------------
--
-- Table structure for table `participant`
--
CREATE TABLE `participant` (
`id` int(11) NOT NULL,
`room_id` int(11) NOT NULL,
`user_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
--
-- Dumping data for table `participant`
--
INSERT INTO `participant` (`id`, `room_id`, `user_id`) VALUES
(1, 1, 1),
(2, 1, 2),
(3, 2, 2),
(4, 2, 3);
-- --------------------------------------------------------
--
-- Table structure for table `room`
--
CREATE TABLE `room` (
`id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
--
-- Dumping data for table `room`
--
INSERT INTO `room` (`id`) VALUES
(1),
(2);
-- --------------------------------------------------------
--
-- Table structure for table `user`
--
CREATE TABLE `user` (
`id` int(11) NOT NULL,
`nickname` varchar(10) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
--
-- Dumping data for table `user`
--
INSERT INTO `user` (`id`, `nickname`) VALUES
(1, 'nick 1'),
(2, 'nick 2'),
(3, 'nick 3'),
(4, 'nick 4');
--
-- Indexes for dumped tables
--
--
-- Indexes for table `participant`
--
ALTER TABLE `participant`
ADD PRIMARY KEY (`id`),
ADD KEY `fk_room_id` (`room_id`),
ADD KEY `fk_user_id` (`user_id`);
--
-- Indexes for table `room`
--
ALTER TABLE `room`
ADD PRIMARY KEY (`id`);
--
-- Indexes for table `user`
--
ALTER TABLE `user`
ADD PRIMARY KEY (`id`);
--
-- AUTO_INCREMENT for dumped tables
--
--
-- AUTO_INCREMENT for table `participant`
--
ALTER TABLE `participant`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;
--
-- AUTO_INCREMENT for table `room`
--
ALTER TABLE `room`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3;
--
-- AUTO_INCREMENT for table `user`
--
ALTER TABLE `user`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;
--
-- Constraints for dumped tables
--
--
-- Constraints for table `participant`
--
ALTER TABLE `participant`
ADD CONSTRAINT `fk_room_id` FOREIGN KEY (`room_id`) REFERENCES `room` (`id`),
ADD CONSTRAINT `fk_user_id` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`);
My non-working queries:
/* I want to exclude users who have a chat with the user with id = 1 */
/*
First attempt gets also users who has a chat with the target user(it's wrong).
But it correctly returns other users(who have no chats at all and who have chats with another users, but not with the target user).
*/
SELECT DISTINCT p.room_id as p_room_id, target_user_participant.room_id as target_user_participant_room_id, u.id as user_id, u.nickname FROM user u
LEFT JOIN participant p ON
u.id = p.user_id
LEFT JOIN participant target_user_participant ON
target_user_participant.user_id = 1
AND
p.room_id = target_user_participant.room_id
WHERE u.id <> 1;
/* Second attempt gets only users who have no chats at all. */
SELECT p.room_id, u.id as user_id, u.nickname, target_user_participant.user_id as target_user_id FROM user u
LEFT JOIN participant p ON u.id = p.user_id
LEFT JOIN participant as target_user_participant ON
target_user_participant.user_id = 1
AND
p.room_id = target_user_participant.room_id
WHERE
p.room_id IS NULL;
/* Third attempt return almost correct values, excluding the chat with the target user(which is good), but it also returns users, who have chats with the first user(which is bad). */
SELECT p.room_id, u.id as user_id, u.nickname, target_user_participant.user_id as target_user_id FROM user u
LEFT JOIN participant p ON u.id = p.user_id
LEFT JOIN participant as target_user_participant ON
target_user_participant.user_id = 1
AND
p.room_id = target_user_participant.room_id
WHERE
target_user_participant.user_id is null;
Expected output:
When I pass the user.id = ‘1’ this would return only users with the IDs [3,4]. Because the second user(with id = 2), has a chat with the first user(with id = 1).
3
Answers
This query joins the user table with the participant table to identify the users who don’t have rooms in common with the user having ID = 1. The subquery user1_rooms retrieves all the room IDs where user 1 is a participant. Then, the main query filters out the rows where the user ID is not 1 and where the room is null
This query should return users who don’t share any rooms with user 1 and also include users who have no rooms at all
sorry, i’m on my phone, does this work for you?
Should be something like that?
But also, have you looked into
where not exists (select * etc.)
?