There's a users table and a matches table. A match consists of a userId1 and userId2 (and its own id). I'm trying to return all the users which are not matched to a certain userId.
So if you have users with ids 1, 2, 3, 4, 5 and matches with 2|5, 2|3, 1|2 then I would expect to get back users with id 3 , 4 and 5 when searching for user with id 1. I don't expect 2 because there's a match with 1 and I don't expect 1 because it's the user itself. I do expect 3 because the match is not with 1, same goes for 5. I also expect 4 because it's not found in the matches table.
I've been looking around how to tackle this and I feel like I'd need to use an EXIST or IN, but I think I'm getting syntax errors because the amount of results I'm comparing are not the same?
select * from Users u
where not exist (select userId1 from Matches m where u.id = m.userId1) and
where not exist (select userId2 from Matches m where u.id = m.userId2)
Please find sample data below, and fiddle of same.
This would be the setup for where I'm at to reproduce:
CREATE TABLE `Users` (
`id` bigint unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(256) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
`createdAt` datetime NOT NULL,
`updatedAt` datetime NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `users_id` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=257 DEFAULT CHARSET=utf8mb3 COLLATE=utf8_unicode_ci;
INSERT INTO `Users` (`id`, `name`, `createdAt`, `updatedAt`)
VALUES
(1, 'Maria', '2021-07-11 00:51:36', '2021-07-11 18:09:58');
INSERT INTO `Users` (`id`, `name`, `createdAt`, `updatedAt`)
VALUES
(2, 'Peter', '2021-07-11 00:51:36', '2021-07-11 18:09:58');
INSERT INTO `Users` (`id`, `name`, `createdAt`, `updatedAt`)
VALUES
(3, 'Sonya', '2021-07-11 00:51:36', '2021-07-11 18:09:58');
INSERT INTO `Users` (`id`, `name`, `createdAt`, `updatedAt`)
VALUES
(4, 'Frank', '2021-07-11 00:51:36', '2021-07-11 18:09:58');
INSERT INTO `Users` (`id`, `name`, `createdAt`, `updatedAt`)
VALUES
(5, 'Johnny', '2021-07-11 00:51:36', '2021-07-11 18:09:58');
CREATE TABLE `Matches` (
`id` bigint unsigned NOT NULL AUTO_INCREMENT,
`userId1` int DEFAULT NULL,
`userId2` int DEFAULT NULL,
`createdAt` datetime NOT NULL,
`updatedAt` datetime NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb3 COLLATE=utf8_unicode_ci;
INSERT INTO `Matches` (`id`, `userId1`, `userId2`, `createdAt`, `updatedAt`)
VALUES
(1, 2, 5, '2021-07-12 21:57:31', '2021-07-12 21:57:31');
INSERT INTO `Matches` (`id`, `userId1`, `userId2`, `createdAt`, `updatedAt`)
VALUES
(2, 2, 3, '2021-07-12 21:57:31', '2021-07-12 21:57:31');
INSERT INTO `Matches` (`id`, `userId1`, `userId2`, `createdAt`, `updatedAt`)
VALUES
(3, 1, 2, '2021-07-12 21:57:31', '2021-07-12 21:57:31');
select * from Users u
where not exist (select userId1 from Matches m where u.id = m.userId1) and
where not exist (select userId2 from Matches m where u.id = m.userId2)