Sql-server – Selecting multiple rows as one row with “AS”

sql server

If I have a table of users, for example, and I want to include the users in a query like so:

SELECT gameId, player1id, player2id, users.username as player1, users.username as player2
FROM players, users
WHERE gameId = 2 AND player1.id = player1id AND player2.id = player2id

How would I handle the part of the select statement where I am getting the player names? I know what I have is incorrect but I am stuck trying to figure out the proper syntax for this.

When all is said and done I am trying to get 1 row to return that has 2 columns on the end named "player1" and "player2" with their respective usernames.

Best Answer

Post the DDL for players (same for users). This is the CREATE TABLE My_Table (fieldname... fieldlist); statement for both tables.

You've no table player1 (or player2) - and then you're trying to use aliases for field names as table names? @RobFarley has given you a clue here (maybe).

I really don't know where to start, except to advise you to read some basic SQL books/sites. Take the site tour, read the "help us to help you" blog (and the links within - bottom left of page and also by @Aaron Bertrand) and visit the help centre (top right).

Tip no.1 - remove your aliases and WHERE clause from your SQL and see what you get (prob. a mess! - CROSS JOIN), but try and work from there. Do you have anyone who can mentor you, at least intially?

Post your table structures (as above), some sample data (in the form of DML - i.e. SQL statements - INSERT INTO My_Table (field_list...) VALUES (Value_list...), the result you want and the logic you used to get that result.