CODEWITHSUNDEEP
mysqlsqldatabaseselectjoin
janb73
janb73

Reputation: 113

MySQL select row with two matching joined rows from another table

Hey I try to select a row from a table with two matching entries on another one. The structure is as following:

-----------------   ---------------------
| messagegroups |   | user_messagegroup |
|               |   |                   |
| - id          |   | - id              |
| - status      |   | - user_id         |
|               |   | - messagegroup_id |
-----------------   |                   |
                    ---------------------

There exist two rows in user_messagegroup with the ids of two users and both times the same messagegroup_id. I would like to select the messagegroup where this two users are inside.

I dont get it.. so I would appreciate some help ;)

Upvotes: 1

Views: 1691

Answers (1)

spencer7593
spencer7593

Reputation: 108370

The specification you provide isn't very clear.

You say "with the ids of two users"... if we take that to mean you have two user_id values you want to supply in the query, then one way to to find the messagegroups that contain these two specific users:

SELECT g.id
     , g.status
  FROM messagegroups g
  JOIN ( SELECT u.messagegroup_id
           FROM user_messagegroup u
          WHERE u.user_id IN (42, 11)
          GROUP BY u.messagegroup_id
         HAVING COUNT(DISTINCT u.user_id) = 2
       ) c
    ON c.messagegroup_id = g.id

The returned messagegroups could also contain other users, besides the two that were specified.

If you want to return messagegroups that contain ONLY these two users, and no other users...

SELECT g.id
     , g.status
  FROM messagegroups g
  JOIN ( SELECT u.messagegroup_id
           FROM user_messagegroup u
          WHERE u.user_id IS NOT NULL
          GROUP BY u.messagegroup_id
         HAVING COUNT(DISTINCT IF(u.user_id IN (42,11),u.user_id,NULL)) = 2
            AND COUNT(DISTINCT u.user_id) = 2
       ) c
    ON c.messagegroup_id = g.id

For improved performance, you'll want suitable indexes on the tables, and it may be possible to rewrite these to eliminate the inline view.

Also, if you only need the messagegroup_id value, you could get that from just the inline view query, without the need for the outer query and the join operation to the messagegroups table.

Upvotes: 1

Related Questions