Geek asks and answers

CHOOSE WHERE TO FIND

I am trying to check the database if a specific combination exists.

Table: conversations

+----+
| id |
+----+
| 1  |
| 2  |
| 3  |
| 4  |
| 5  |
+----+

Table: conversations_users

+----+--------------+------+
| id | conversation | user |
+----+--------------+------+
| 1  | 1            | 1    |
| 2  | 1            | 2    |
| 3  | 2            | 1    |
| 4  | 2            | 2    |
| 5  | 2            | 3    |
| 6  | 2            | 4    |
| 7  | 3            | 2    |
| 8  | 3            | 3    |
| 9  | 4            | 2    |
| 10 | 4            | 4    |
+----+--------------+------+

Then I want to make a request to find out where these users are in the same chains:

Users: 1,2,3,4 (Only them, no else)

If there is a conversation in which only those are located, I want to get the identifier of this conversation, but I also resulthave to become0

Anyone have any ideas how to do this trick?

+5
source share
9 answers

This is an example of a set-in-sets request. For this, I like to use group bywith a sentence having:

select conversation
from conversation_users cu
group by conversation
having SUM(user = 1) > 0 and
       sum(user = 2) > 0 and
       sum(user = 3) > 0 and
       sum(user = 4) > 0 and
       sum(user not in (1, 2, 3, 4)) = 0

Each sentence condition has one of five conditions asked in the question:

  • user 1 is in conversation
  • user 2 is in conversation
  • user 3 is in conversation
  • 4
+2

, . , IN, , , :

SELECT id
FROM conversations_users
WHERE user in (1, 2, 3, 4)
GROUP BY id
HAVING COUNT(DISTINCT user) = 4

, , 3 4 . , :

SELECT id
FROM conversations_users
WHERE user in (1, 2, 3, 4)
GROUP BY id
HAVING COUNT(DISTINCT user) <= 4
+3
SELECT
  cs.conversation,
  IF(csl.total = 4,'yes','no') AS AllIn
FROM conversations_users AS cs
  LEFT JOIN (
                SELECT 
                    conversation , 
                    COUNT(DISTINCT user) AS total 
                FROM conversations_users 
                WHERE user IN (1,2,3,4) 
                GROUP BY conversation
            ) AS csl
    ON csl.conversation = cs.conversation
GROUP BY cs.conversation

SQL Fiddle Demo

| CONVERSATION | ALLIN |
------------------------
|            1 |    no |
|            2 |   yes |
|            3 |    no |
|            4 |    no |


| CONVERSATION | ALLIN |
------------------------
|            1 |     0 |
|            2 |     2 |
|            3 |     0 |
|            4 |     0 |
+2

, , :

SELECT cu.conversation
FROM (select conversation, count(distinct user) usercnt 
      from conversations_users
     group by conversation) t
  JOIN conversations_users cu on t.conversation = cu.conversation
WHERE cu.user in (1, 2, 3, 4) AND t.usercnt = 4
GROUP BY cu.conversation
HAVING COUNT(DISTINCT cu.user) = 4

SQL Fiddle Demo

, , . , , 1,2,3 4.

+2

, :

SELECT
  conversation
FROM
  conversations_users
GROUP BY
  conversation
HAVING
  COUNT(
    DISTINCT CASE WHEN user IN (1,2,3,4) THEN user END
  )=4 AND
  COUNT(DISTINCT user)=4
+2

, users:

SELECT id
FROM conversations AS c
WHERE NOT EXISTS
      ( SELECT *
        FROM users AS u
        WHERE u.id IN (1, 2, 3, 4)
          AND NOT EXISTS
              ( SELECT *
                FROM conversations_users AS cu 
                WHERE cu.user = u.id
                  AND cu.conversation = c.id
              )
      ) 
  AND NOT EXISTS 
      ( SELECT *
        FROM conversations_users AS co        -- and only them
        WHERE co.conversation = c.id 
          AND co.user NOT IN (1, 2, 3, 4)
      ) ;

users (can'see why, ), :

WHERE NOT EXISTS
      ( SELECT *
        FROM users AS u
        WHERE u.id IN (1, 2, 3, 4)
          AND NOT EXISTS

:

WHERE NOT EXISTS
      ( SELECT *
        FROM (SELECT 1 AS id UNION SELECT 2 UNION
              SELECT 3       UNION SELECT 4) AS u
        WHERE NOT EXISTS

, , MySQL ( ). GROUP BY / COUNT, , , , , . ( 10), , : SQL has-many-through MySQL, . , 5 6 MySQL (, , ).

, , / () , 5 :

SELECT id
FROM conversations AS c
WHERE  EXISTS (SELECT * FROM conversations_users AS cu
               WHERE  cu.conversation = c.id AND cu.user = 1)
AND    EXISTS (SELECT * FROM conversations_users AS cu
               WHERE  cu.conversation = c.id AND cu.user = 2)
AND    EXISTS (SELECT * FROM conversations_users AS cu
               WHERE  cu.conversation = c.id AND cu.user = 3)
AND    EXISTS (SELECT * FROM conversations_users AS cu
               WHERE  cu.conversation = c.id AND cu.user = 4)
AND    NOT EXISTS (SELECT * FROM conversations_users AS cu
                   WHERE  cu.conversation = c.id AND cu.user NOT IN (1,2,3,4))
+2

.

- 5 , , 4 (1,2,3,4) , , , 4 .

DEMO

select distinct
    cu.conversation
from
    conversations_users cu
        left join
    conversations_users cu2 ON cu.conversation = cu2.conversation
where
    cu.user in (1 , 2, 3, 4)
        and cu2.user in (1 , 2, 3, 4)
        and cu.user != cu2.user /* include this clause if you need to exclude conversations of a user with themselves */

, , 4 .

? , " 0", ? , :

select distinct
    case
        when
            cu.user in (1 , 2, 3, 4)
                and cu2.user in (1 , 2, 3, 4)
        then
            cu.conversation
        else 0
    end conversation
from
    conversations_users cu
        left join
    conversations_users cu2 ON cu.conversation = cu2.conversation
where
    1 = 1 
            and cu.user != cu2.user /* include this clause if you need to exclude conversations of a user with themselves */
+2
SELECT ID FROM CONVERSATIONS WHERE ID IN 
(SELECT CONVERSATIONS FROM CONVERSATION_USERS 
GROUP BY CONVERSATIONS HAVING COUNT(DISTINCT USER) >= 2)
+2

If I read your requirements correctly, you want to indicate the identifier of any conversation that contains only such people (for example) 1,2,3 and 4, and all these people in it. If you do not want 0 to return to this conversation.

If so, then something like this

SELECT CASE WHEN MatchCount = 4 AND UnMatchCount IS NULL THEN conversations.id ELSE 0 END
FROM conversations
LEFT OUTER JOIN (SELECT conversation, COUNT(DISTINCT user) AS MatchCount FROM conversations_users WHERE user IN (1,2,3,4) GROUP BY conversation) Sub1 ON conversations.id = Sub1.conversation
LEFT OUTER JOIN (SELECT conversation, COUNT(DISTINCT user) AS UnMatchCount FROM conversations_users WHERE user NOT IN (1,2,3,4) GROUP BY conversation) Sub2 ON conversations.id = Sub2.conversation

EDIT is a modified version of the above request to return conversation identifiers in which only these 4 users participate. Playing around it seems to be a pretty effective way to do it.

SELECT conversations.id 
FROM conversations
LEFT OUTER JOIN (SELECT conversation, COUNT(DISTINCT user) AS MatchCount FROM conversations_users WHERE user IN (1,2,3,4) GROUP BY conversation) Sub1 ON conversations.id = Sub1.conversation
LEFT OUTER JOIN (SELECT conversation, COUNT(DISTINCT user) AS UnMatchCount FROM conversations_users WHERE user NOT IN (1,2,3,4) GROUP BY conversation) Sub2 ON conversations.id = Sub2.conversation
WHERE MatchCount = 4 
AND UnMatchCount IS NULL
+2
source

More articles:


All Articles