Geek asks and answers

SQL: a table with several foreign keys associated with the same primary (2)

Just to expand on my previous problem - I have two tables in my database and I want to extract certain information. Tables below:

   (player): player_id (primary), playerName
    (match): match_id (primary), playerID1, playerID2, playerID3, scorer etc..

You have provided me with the following code to get player names:

SELECT p.Name 
FROM `match` m
INNER JOIN `player` p 
    ON p.player_id IN (m.playerID1, m.playerID2, m.playerID3) //etc

It works with gratitude - there are only two settings that I would like to make:

1) I would like to return the name of the scorer, as well as the names of the players. Since m.scorer is an identifier, how do I match it with the p.Name attribute if p.Name is already mapped to m.playerID? 2) The above query returns the names of all players. Soon I will add a search function where you will find all matches for a specific player. Is there any exception to this player from the results (seeing that we already know that this player was in the match when he was searched). Thus, to a large extent, all matches and other players for a specific player are returned, but exclude the actual player search from the results. Sorry if this is unclear, let me know and I will expand it.

Thanks again for the great help

+3
source share
1 answer

- ( ). , matchId , :).

SELECT p.Name as 'Player Name', m.matchId as 'Match Id',
CASE s.scorerId WHEN ISNULL(S.SCORERID,0) THEN 'YES' ELSE 'NO' END as 'Scored?',
s.name as 'Scorer Name' --as requested from comment
FROM match m
INNER JOIN player p ON p.player_id IN (m.playerID1, m.playerID2, m.playerID3)
LEFT JOIN scorer s ON s.matchId = m.matchId
    AND s.player_id IN (m.playerID1, m.playerID2, m.playerID3)

. SQLFiddle

+3

More articles:


All Articles