CODEWITHSUNDEEP
phpmysqlsqldatabasejoin
user74847
user74847

Reputation: 361

MySQL Find an ID based on 2 ids

i would like to see if player A (9) and player B (14) have ever both entered the same round, one round has many entries by many players. this is the middle table of a many to many relationship between rounds and players

table: entries
id | roundID | PlayerID   
5  | 7       | 14
4  | 6       | 2
3  | 5       | 14
2  | 5       | 9
1  | 4       | 9

Im looking to return round ID 5 obviously, but what SQL statement does this need? a JOIN?

i could do it by getting all rounds played by player A and B seperately looping through As rounds and looping through Bs rounds on each iteration of A to look for a match, but that seems needlessly costly.

Upvotes: 0

Views: 71

Answers (3)

user2506061
user2506061

Reputation:

Use INNER JOIN with subqueries as follow

SELECT * FROM (SELECT * FROM tests WHERE player_id='9') t9 INNER JOIN (SELECT * FROM tests WHERE player_id='14') t14 ON t9.round_id = t14.round_id

Upvotes: 0

joyleak
joyleak

Reputation: 58

Something like this should work, basically getting a count of all the PlayerID enteries per roundID for only the specified players and restricting to show only ones with multiples.

SELECT
    roundID
FROM
    entries
WHERE
    PlayerID IN (9, 14)
GROUP BY
    roundID
HAVING
    COUNT(*)>1

Upvotes: 1

Ed Gibbs
Ed Gibbs

Reputation: 26343

If I understand the question correctly, something as simple as a SELECT DISTINCT will work here:

SELECT DISTINCT roundID
FROM entries
WHERE PlayerID IN (9, 14)

Upvotes: 0

Related Questions