SQLite many to many selection

Question

I'm trying to select data from my android sqlite database. I have 3 tables - table Players (list of all players), table Matches (list of all matches) and table PlayersMatches (list of players in each match, created as many to many relationship). I want to write SELECT command, which returns all matches, where played two players together. Here is a little example:

Table PLAYERS
-------------
id    name
1     John
2     Jamie
3     Joe

Table MATCHES
-------------
id    date
1     17/12/01
2     17/12/02
3     17/12/03

Table PLAYERS_MATCHES
---------------------
id    id_Player    id_Match
1     1            1
2     2            1
3     1            2
4     3            2
5     1            3
6     2            3

So, I want to write select command, which return all matches, where played John (1) and Jamie (2). Result should be:

SELECT RETURNED
-------------
id_match
1
3

Note, that there can be more than 2 players in each match.

It is possible to write a single sql command or I should do it in different way? (for example select command for the first player, another for the second player and programatically in Android filter theese matches).

Answer 1

It is perfectly OK to do it in single sql. This is the kind of queries SQL was built for.

You do a self join on PlayerMatches table like:

SELECT distinct M1.id_match
From PLAYERS_MATCHES M1 CROSS JOIN PLAYERS_MATCHES M2  
WHERE M1.id_match=M2.id_match AND M1.id_player=1 AND M2.id_player=2

Verification:

sqlite> select * from matches;
id          p           m
----------  ----------  ----------
1           1           1
2           2           1
3           1           2
4           3           2
5           1           3
6           2           3
sqlite>

sqlite> select distinct m1.m from matches m1 cross join matches m2
   ...> where m1.m=m2.m and m1.p=1 and m2.p=2;
m
----------
1
3
sqlite>