SQL: Find the teams containing a list users

Question

I have a game where I have teams containing a number of users. There are different amounts of users in each team but for anyone team the combination of users makes that team unique.

I have created three database tables for this.

 Team    User    TeamUsers
 t_id    u_id    t_id, u_id

If users 1, 4, 5 and 7 have been in a team together previously I would like to know the team ID of that team. What would the SQL look like for this?

I have experimented with something like this:

SELECT t_id FROM teamusers WHERE u_id IN (users[1], ..., users[i])
GROUP BY t_id HAVING COUNT(t_id) = users[].length;

users array is programatically filled with the values. Does this seem correct? Is there another way that's better?

How do you handle change over time? How do we know that users were member of a team *at the same time*? Does every change in members automatically create a *new* team (different `t_id`)? — Erwin Brandstetter, Apr 14 '13 at 01:15
Yes a change in users creates a new team. It works kind of like the SC2 teamup if you have played that game? If you play with some one that creates a team. — Pablo Jomer, Apr 14 '13 at 10:42

score 1 · Accepted Answer · edited May 23 '17 at 12:04

This is a classical case of relational division. We have assembled a whole arsenal of queries under this closely related question:
How to filter SQL results in a has-many-through relation

One way to get all teams that have all given players would be what you already have. Should be among the most efficient queries.

For longer arrays, unnest() + JOIN performs better:

SELECT t_id
FROM  (SELECT unnest($users) AS u_id) u
JOIN   team_users USING (u_id)
GROUP  BY 1
HAVING count(*) = array_length($users, 1);

But for just a hand full of players, it hardly matters.
For repeated use, instead of building the query in your app, you can create a table function:

CREATE OR REPLACE FUNCTION f_get_teams(_users int[])
  RETURNS SETOF int AS
$func$

SELECT t_id
FROM  (SELECT unnest($1) AS u_id) u
JOIN   team_users USING (u_id)
GROUP  BY 1
HAVING count(*) = array_length($1, 1);

$func$ LANGUAGE SQL STRICT;

Call:

SELECT * FROM f_get_teams('{1,4,5,7}'::int[]);

SQL: Find the teams containing a list users

1 Answers1