CODEWITHSUNDEEP
phpmysqlsql
Jovan
Jovan

Reputation: 4794

mysql join master table with another by foreign keys

In my MySQL database I have one master table with PK id_duel and another table with PK id_duel_player and two FK's id_player and id_duel.

For every id_duel I have two players with the same id_duel (two players make one duel).

I want to build this statement: Give me the duel (id_duel) consisted of this two players ($id_player1and $id_player2- this are id_player of two different players that participated in required duel)

mySql tables

Can anyone help my with SQL statement?

Upvotes: 0

Views: 125

Answers (1)

Jeff Rosenberg
Jeff Rosenberg

Reputation: 3572

You should be able to use a JOIN to make sure the two players have the same id_duel:

SELECT id_duel
FROM duels d
INNER JOIN duel_player dp1
  ON d.id_duel = dp1.id_duel
    AND dp1.id_player = @id_player1
INNER JOIN duel_player dp2
  ON d.id_duel = dp2.id_duel
    AND dp2.id_player = @id_player2

Upvotes: 1

Related Questions