Geek asks and answers

Select rows that have no other rows

It seemed pretty simple, but it was getting uncomfortable.

Suppose we have a table containing ...

+---------+-----------+
| chat_id | friend_id |
+---------+-----------+
| A       |         1 |
| A       |         2 |
| A       |         3 |
| B       |         1 |
| B       |         2 |
| C       |         1 |
| C       |         2 |
| C       |         3 |
| D       |         1 |
| D       |         2 |
| D       |         3 |
| D       |         4 |
| D       |         5 |
| E       |         0 |
| E       |         1 |
| E       |         2 |
| E       |         3 |
| E       |         4 |
| E       |         5 |
| E       |         6 |
| E       |         7 |
| F       |         0 |
| F       |         1 |
| G       |         1 |
| G       |         2 |
+---------+-----------+

And I want to select only those chat_id that have friend_ids 1 and 2, and there is no other friend_id, what would SQL get B and G?

So far, the best I've come up with is:

SELECT DISTINCT a.chat_id, COUNT(*) 
FROM tt2 a 
LEFT JOIN tt2 b 
ON a.chat_id = b.chat_id 
AND b.friend_id NOT IN (1,2) 
WHERE a.friend_id in (1,2) 
and b.chat_id IS NULL GROUP BY a.chat_id HAVING COUNT(*) = 2;

+---------+----------+
| chat_id | count(*) |
+---------+----------+
| B       |        2 |
| G       |        2 |
+---------+----------+
2 rows in set (0.00 sec)

And just in case, I was looking for chat_id where only 1,2,3 exists ...

SELECT DISTINCT a.chat_id, COUNT(*) 
FROM tt2 a 
LEFT JOIN tt2 b 
ON a.chat_id = b.chat_id 
AND b.friend_id not in (1,2,3) 
WHERE a.friend_id IN (1,2,3) 
AND b.chat_id IS NULL 
GROUP BY a.chat_id 
HAVING COUNT (*) = 3;

+---------+----------+
| chat_id | count(*) |
+---------+----------+
| A       |        3 |
| C       |        3 |
+---------+----------+

But this table can become massive, and I need SQL to be fast, does anyone know a better way?

Try and clarify ... I am given a bunch of friend_id, and I want to get chat_id, where only those friend_id exist for this chat_id .... with fast SQL (in sqlite)

Thank you very much in advance!

+3
source share
2 answers

,

SELECT 
    d.chat_id,
    COUNT(DISTINCT s.friend_id) AS matchedFriends,
    COUNT(DISTINCT d.friend_id) AS totalFriends
FROM tt2 AS d
INNER JOIN tt2 AS s
    ON s.chat_id = d.chat_id
    AND s.friend_id IN (1,2)
GROUP BY d.chat_id
HAVING matchedFriends = 2
AND totalFriends = matchedFriends

INNER JOIN s , , . matchedFriends , .

totalFriends , .

, HAVING , 2 , .

, , , , .

(chat_id,friend_id) ( , 2 )

+1

:

SELECT chat_id, GROUP_CONCAT(DISTINCT friend_id ORDER BY friend_id) AS friends 
FROM table_1
GROUP BY chat_id
HAVING friends = '1,2'

. mysql, , sqlite.

0

More articles:


All Articles