CODEWITHSUNDEEP
mysqlsql
Padre
Padre

Reputation: 1

MYSQL - selecting data from one table based on the content of the other one

So, I have a database with 2 tables with the details of people competing, playing games. Total of 15 players, new games added to the database every week.

Table 1 (name: PlayerList)

PlayerID | PlayerNickname
1          AaAa
2          Baba
3          Caca
4          Dada
5          EaEa
...

Table 2 (name: GamesHistory)

GameDate | Position1 | Position2 | Position3

2019-01-02 AaAa        CaCa        Baba
2019-01-03 Eaea        CaCa        Dada
2019-01-04 Dada        Baba        CaCa
2019-01-05 AaAa        Eaea        Baba
2019-01-05 Dada        Aaaa        Eaea
...

I am looking for one query that will allow me to select only the games (records) in which the player with a specific PlayerID (eg. 4) has taken any of the 3 positions.

Upvotes: 0

Views: 27

Answers (1)

GMB
GMB

Reputation: 222632

You can join as follows:

select g.*
from GamesHistory g
inner join PlayerList p 
    on p.PlayerNickName in (g.Position1, g.Position2, g.Position3)
where p.PlayerID = 4

Side note: you should probably be storing the id of the players in the games table rather than their names. In the real world, names are not guaranteed to be unique, whereas the id of the player seems to (and should) be the primary key of the players table, which makes it ideal to be used as a foreign key in the games table.

Upvotes: 0

Related Questions