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