0

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   
1
-- 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
  • 1
    Assuming that there is a unique constraint on (teamid, userid), then you don't need the DISTINCT. – ypercubeᵀᴹ Jun 17 at 9:42
  • This does answer the question and I get the teamids that they are both part of. Thankyou. But unfortunately I don't get teamuser.isadmin - Well at least I can do one more query afterwards, but it would be awesome to get the userid and isadmin columns also. – Raf A. Jun 17 at 10:00
  • I know what isadmin means, but I don't know what's your expectation. Could you put your expected result in the OP? – mingchau Jun 17 at 10:14
1

One ide is to first find out which teams that has two or more members:

SELECT teamid
FROM teamuser
GROUP BY teamid
HAVING COUNT(1) >= 2

If you are only interested in, say user 1 and 2:

SELECT teamid
FROM teamuser
WHERE userid IN (1,2)
GROUP BY teamid
HAVING COUNT(1) >= 2

Now, you can join this with your teamuser table:

SELECT a.teamid, a.userid, a.isadmin, a.option
FROM teamuser a
JOIN (
    SELECT teamid
    FROM teamuser
    WHERE userid IN (1,2)
    GROUP BY teamid
    HAVING COUNT(1) >= 2
) as b
    ON a.teamid = b.teamid
-- WHERE a.userid IN (1,2)
ORDER BY a.teamid, a.userid        

Worth noting in your model is that a team can have several admins, but that is perhaps intentionally?

  • Yes thanks it is intentional. Beware the options is on the teams table, so it's a JOIN to the teams also. But for some reason, your current code (second example with JOIN) returns also more userids, not just 1 and 2. – Raf A. Jun 17 at 11:16
  • 1
    Yes, it will return all users in that team. Add where userid in (1,2) to the outer select as well. – Lennart Jun 17 at 11:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.