Query table based on unique multiple column results set

Viewed 38

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)
1 Answers

You can do it with a self join of Users that will return all possible combinations of 2 users and then a LEFT join to Matches and filter out the matching rows:

SELECT u1.*
FROM `Users` u1 
INNER JOIN `Users` u2 ON u1.id <> u2.id
LEFT JOIN `Matches` m ON (m.userId1, m.userId2) IN ((u1.id, u2.id), (u2.id, u1.id))
WHERE m.id IS NULL AND u2.id = ?

Change ? to the user's id you search for.
See the demo.

Related