MySQL ...

chezzer

Hi
I have 2 Db's ... players + matches ..

Players is primary key ID, name, sex,

Mathes is , Player1 ID, Player2ID, result, score,location,date

linking these 2 tables by the following

SELECT p.Name,p.Name,m.Result,m.Score,m.location,
m.date 
 from matches m, players p 
 WHERE p.Id = m.Player1 
and p.Id = m.Player2

doesn't work, I can see why ... but how else can i link these without having a seperate table for each player ?

marco_polo

You need to join twice on the players table once for player 1 and once for player 2. Currently the logic is asking it to return all records from the player table where the id is equal to two different player ids, which does not make sense.

SELECT p1.Name,p2.Name,m.Result,m.Score,m.location,
m.date 
 from matches m, players p1,players p2
 WHERE p1.Id = m.Player1 
and p2.Id = m.Player2

chezzer

ah ok , so i dont need 2 tables for player1 and player2 ?

ok thanks ... that makes sense ..