SQL combining tables to single rows

A problem with combining tables into single rows.

TABLE GAMES:

ID	player1_ID	player2_ID	des
11	23	68	something
82	34	23	whatever
302	13	34	mmmmmmmm

TABLE PLAYERS:

ID	alias
23	Jeex
34	Jack
68	Jill
13	John

The key I start my query with is one of the Player ID’s.

I want to show all the games that Player ID=23 is playing in. That can be as Player1 or Player2.

I also tried with a cross table,

TABLE CROSS:

ID	player_ID	game_ID
1	23	11
2	68	11
3	34	82
2	23	82
3	13	302
3	34	302

but without results.

The issue is that I want the aliasses and other players data of in the output as well as some extra board data, without having to deal with multiple rows per game.

So, the requested output would be like:

game_ID	player1_ID	player2_ID	des	alias1	alias2
11	23	68	something	Jeex	Jill
82	34	23	whatever	Jack	Jeex

I tried several approaches, but can’t figure this out. Thanks for the help.

Answer

You must join games to 2 copies of players, so that you can get both aliases and filter games in the WHERE clause to get the rows only for the player that you want:

SELECT g.*, 
       p1.alias alias1,
       p2.alias alias2 
FROM games g
INNER JOIN players p1 ON p1.id = g.player1_ID
INNER JOIN players p2 ON p2.id = g.player2_ID
WHERE ? IN (g.player1_ID, g.player2_ID)

Change ? to the player’s id that you want.