Mysql – Select users that are in the same team

MySQLmysql-5.7

We have these 3 tables:

USERS

userid
name
email

TEAMS

teamid
name
options (0,1,2 or 3)

TEAMUSER

id
teamid
userid
isadmin (0 or 1, if they are administrator of team)

Let's say we have user 1, 2 and 3 inside users table. Lets also say we have 3 teams, team 1, 2 and 3.

User 1 and 2 are part of the same team, the team id 3. So there are rows in teamuser table also.
User 1 is also part of team 2, and user 3 is part of team 3, but it's not relevant here.

THE QUESTION IS THIS:

How do I get an SQL that says:
SHOW ME THE TEAMS WHERE USER 1 AND 2 (OR X AND Y) ARE PART OF THE SAME TEAM? (and isadmin to see if they are admin and the team options also)

So user 1 and 2 can be part of more than 1 team together, they can be together in several teams, and that's what I'm interested in. Not the teams that they are individually in, but only the teams that they are both in.

So it would be awesome to get a list of rows like this if it's possible in one query:

teamid | userid | isadmin | options
-----------------------------------
  3    |   1    |    1    |    1   
  3    |   2    |    0    |    1

Best Answer

-- get all the unique team ids which have both user1 and user2
SELECT DISTINCT a.teamid 
FROM
( SELECT teamid FROM `TEAMUSER` WHERE userid = user1 ) AS a
INNER JOIN 
( SELECT teamid FROM `TEAMUSER` WHERE userid = user1 ) AS b 
ON a.team_id = b.team_id

Related Solutions

Mysql – Unexpected extremely long query time (~5 minutes using nested WHEN-INs)

Let's start with your original query

   SELECT *  
    FROM reports 
    WHERE report_name IN ( 
        SELECT report_name  
        FROM reports  
        WHERE region IN ( 
            SELECT distinct region  
            FROM macroregions 
            WHERE macroregion IN ( 
                SELECT distinct macroregion 
                FROM users  
                WHERE callsign = '$thisuser' 
            ) 
        )     
    )

You can gather keys only (in stages), then join the keys with the reports table. Here is my new proposed query

SELECT reports.* FROM
(
    SELECT rpts.report_name FROM
    (
        SELECT DISTINCT regions.region FROM
        (SELECT DISTINCT macroregion
        FROM users WHERE callsign = '$thisuser') users
        INNER JOIN
        (SELECT DISTINCT macroregion,region FROM regions) regions
        USING (macroregion)
    ) regionkeys
    INNER JOIN
    (SELECT region,report_name FROM reports) rpts
    USING (region)
) reportnamekeys
INNER JOIN reports
USING (report_name);

If you are not comfortable with the USING clause here is my new propsed query without using the USING clause:

SELECT reports.* FROM
(
    SELECT rpts.report_name FROM
    (
        SELECT DISTINCT regions.region FROM
        (SELECT DISTINCT macroregion
        FROM users WHERE callsign = '$thisuser') users
        INNER JOIN
        (SELECT DISTINCT macroregion,region FROM regions) regions
        ON users.macroregion = regions.macroregion
    ) regionkeys
    INNER JOIN
    (SELECT region,report_name FROM reports) rpts
    ON regionkeys.region = rpts.region
) reportnamekeys
INNER JOIN reports
ON reportkeys.report_name = reports.report_name;

You will need some indexes to support the subqueries

ALTER TABLE users   ADD INDEX callsign_macroregion_ndx (callsign,macroregion);
ALTER TABLE regions ADD INDEX macroregion_region_ndx (macroregion,region);
ALTER TABLE reports ADD INDEX region_report_name_ndx (region,report_name);
ALTER TABLE reports ADD INDEX report_name_ndx (report_name);

The first 3 indexes are called covering indexes. They are called such because the subqueries calls for only those exact columns. Therefore, no need to read from the table. The data is only fetched from the index.

You should also study how to refactor queries to gather keys, perform WHERE clauses early, and perform JOINs last. Here is great YouTube Video on how to do this: http://youtu.be/ZVisY-fEoMw (Based on this book : Refactoring SQL Applications)

Give it a Try !!!

MySQL: select a row if that row is equal for every user

If there are 5 rows per month (1 for every user), this will do:

SELECT m.year, m.month 
FROM month AS m
GROUP BY m.year, m.month
HAVING SUM(finished) = 5 ;

Best Answer

Related Solutions

Mysql – Unexpected extremely long query time (~5 minutes using nested WHEN-INs)

MySQL: select a row if that row is equal for every user

Related Question