Get only users who don't share chat rooms with the X user-CodePudding

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 rooms\chats(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).

CodePudding user response：

sorry, i’m on my phone, does this work for you?

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
        INNER JOIN participant as target_user_participant
            ON target_user_participant.user_id = 1
            AND p.room_id = target_user_participant.room_id
    ) ON u.id = p.user_id
WHERE target_user_participant.user_id is null;

Should be something like that?

But also, have you looked into where not exists (select * etc.)?

CodePudding user response：

This query should return users who don't share any rooms with user 1 and also include users who have no rooms at all

SELECT DISTINCT u.id, u.nickname
FROM user u
LEFT JOIN participant p ON u.id = p.user_id
LEFT JOIN participant p1 ON p1.room_id = p.room_id AND p1.user_id = 1
WHERE (p1.user_id IS NULL OR u.id = 1) AND u.id != 1

CodePudding user response：

SELECT u.id AS user_id, u.nickname
FROM user u
LEFT JOIN participant p ON u.id = p.user_id
LEFT JOIN (
    SELECT DISTINCT room_id
    FROM participant
    WHERE user_id = 1
) user1_rooms ON p.room_id = user1_rooms.room_id
WHERE p.user_id != 1 AND user1_rooms.room_id IS NULL;

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