is this sort of join possible?

i have 2 tables like this:

user table:

| user_id | forename | surname |
--------------------------------------
| 1 | john | smith |
| 2 | susan | jones |
| 3 | gary | walsh |
--------------------------------------
results table:

| user1 | user2 | score |
--------------------------------------
| 2 | 3 | 20 |
| 1 | 2 | 50 |
--------------------------------------

i need an sql which will give join forename and surname on both user1 and user 2 column

so results of joining line 1 of results would be:

user1_forename = susan;
user1_surname = jones;
user2_forename = gary;
user2_surname = walsh;
score = 20;

ive been trying something like this:

select u.forename as user1_forename,u.surname as user1_surname,
user2_forename,u.surname as user2_surname
from results r
inner join users u
on r.user1 = u.user_id
and r.user2 = u.user_id
...

but not working. is this possible & if not what can i do please!

Try This

SELECT [User].[forename] AS User1_forename, [User].[surname] AS User1_surname, User2.forename AS User2_forename, User2.surname AS USer2_surname, [Results].[Score]
FROM [User] AS User2 INNER JOIN ([User] INNER JOIN Results ON [User].[user_id]=[Results].[user1]) ON User2.user_id=[Results].[user2];


S-

I changed some of your variable names (fname instead of forename, lname instead of surname).
tables are z_score (your results table) and z_user (your users table).
Try this:

If you order by z_score.score, it will parse the data in groups so they are used one at a time.
Your output will be:

To just get names simply drop uid1 & uid2 out of the select statement. Hope this helps!

aha! i was almost there. (well...sort of... )


many thanks for your expert help