Is it possible to create a SELECT for this?

I have (2) tables.

• match has (4) player IDs (pid1,pid2,pid3,pid4) and points for each player. (primary key match_id).

• player table has pid and the full name.

• I want to create a SQL select to full names and points for a given match.

• Create another SQL to select to full names and points for a given player id.

Currently, I have to use multiple SELECT and PHP.

Answer

The suboptimal table structure is what convolutes this a little bit, but what you’re looking for seems, essentially, a bunch of inner joins from each of the columns on match to the player table. Here’s a sample set of inner joins – each time I join to the player table, I alias it in a new way. In the select statement, then, you can choose WHICH instance of the player table you’re looking for. Something like:

SELECT a.FullName, m.player1points, b.FullName, m.player2points, c.FullName, m.player3points, d.FullName, m.player4points
FROM dbo.match AS m
INNER JOIN dbo.player AS a ON m.pid1 = a.pid
INNER JOIN dbo.player AS b ON m.pid2 = b.pid
INNER JOIN dbo.player AS c ON m.pid3 = c.pid
INNER JOIN dbo.player AS d ON m.pid4 = d.pid
WHERE m.Match_ID = <yourMatchId>

If you’re searching not for a match, but a player, your where clause will have to include all four columns, separated by OR.

If you have control over the table structure, consider redesigning a little bit. Matches ultimately have a many-to-many relationship with players (there are multiple players in a match, and a player can participate in multiple matches). To that end, you may want a match table, a player table, and a junction table. A junction table is basically a list of match-player keys, each entry representing A player playing in A match; whereas player or match-specific data should live on their respective tables.