MySQL select row with two matching joined rows from another table

janb73

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 ;)

spencer7593

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.

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at
0

Comments

0 comments
Login to comment

Related

From Dev

How to select non-matching rows from two mysql table

From Dev

See if two table rows exist, inner joined to another row

From Dev

MySQL: Update rows in table, from rows with matching key in another table

From Dev

mysql: select rows from another table as columns

From Dev

Delete rows in MySQL matching two columns in another table

From Dev

Delete rows in MySQL matching two columns in another table

From Dev

How to SELECT none matching rows from one table to another?

From Dev

Unable to select data from another joined table

From Dev

Select partialy matching rows from two differernt table in KDB?

From Dev

Efficiently deleting rows from one table where not matching another [MySQL]

From Dev

MYSQL: select all rows from table matching a combination and the opposite

From Dev

Taking ids from two rows of one MySQL table and entering those values in one row of another MySQL table using PHP

From Dev

select rows which has latest date without repetition from two joined table?

From Java

Using data.table to select rows by distance from another row

From Dev

Selecting two rows from another table using one row

From Dev

MySQL: combine 2 rows from another table into a single result row

From Dev

MySql select joined rows that don't exists in third table

From Dev

Insert rows into a table from another two tables separately in mysql

From Dev

mysql select from table where two (or more) rows match,

From Dev

Select rows according row type in another table

From Dev

MySQL - How to select only two columns from a row in one table

From Dev

Replace all rows of a select query with matching rows of another table

From Dev

Retrieve records with no matching rows in joined table

From Dev

How to find single row data from joined table in mysql?

From Dev

how to grab the not equal row from joined table in mysql?

From Dev

Make a SELECT query from two tables with one record from matching rows in mysql

From Dev

how to select one row from one table and multiple rows from other table using joins in mysql,

From Dev

MySQL-Need to select a row from one table to another table WHERE a <# will generate in SELECT statement

From Dev

Mysql select rows comaparing with another table records

Related Related

  1. 1

    How to select non-matching rows from two mysql table

  2. 2

    See if two table rows exist, inner joined to another row

  3. 3

    MySQL: Update rows in table, from rows with matching key in another table

  4. 4

    mysql: select rows from another table as columns

  5. 5

    Delete rows in MySQL matching two columns in another table

  6. 6

    Delete rows in MySQL matching two columns in another table

  7. 7

    How to SELECT none matching rows from one table to another?

  8. 8

    Unable to select data from another joined table

  9. 9

    Select partialy matching rows from two differernt table in KDB?

  10. 10

    Efficiently deleting rows from one table where not matching another [MySQL]

  11. 11

    MYSQL: select all rows from table matching a combination and the opposite

  12. 12

    Taking ids from two rows of one MySQL table and entering those values in one row of another MySQL table using PHP

  13. 13

    select rows which has latest date without repetition from two joined table?

  14. 14

    Using data.table to select rows by distance from another row

  15. 15

    Selecting two rows from another table using one row

  16. 16

    MySQL: combine 2 rows from another table into a single result row

  17. 17

    MySql select joined rows that don't exists in third table

  18. 18

    Insert rows into a table from another two tables separately in mysql

  19. 19

    mysql select from table where two (or more) rows match,

  20. 20

    Select rows according row type in another table

  21. 21

    MySQL - How to select only two columns from a row in one table

  22. 22

    Replace all rows of a select query with matching rows of another table

  23. 23

    Retrieve records with no matching rows in joined table

  24. 24

    How to find single row data from joined table in mysql?

  25. 25

    how to grab the not equal row from joined table in mysql?

  26. 26

    Make a SELECT query from two tables with one record from matching rows in mysql

  27. 27

    how to select one row from one table and multiple rows from other table using joins in mysql,

  28. 28

    MySQL-Need to select a row from one table to another table WHERE a <# will generate in SELECT statement

  29. 29

    Mysql select rows comaparing with another table records

HotTag

Archive