joining on two userid's

[svcghost]

Hello. I have two tables: users and actions
One action contains two users so there are two primary keys user1id and user2id (which are foreign keys from the users table)

How would I go about joining these tables in a query where I want to select each of the users' last names? The last names are a field in the users table.

Example query:

SELECT users.lastname
FROM (actions LEFT JOIN users ON actions.user1id = users.userid) 
LEFT JOIN users ON actions.user2id = users.userid

This does not work however.

All help is greatly appreciated


I write sql directly, no wizard stuff.

[HiTechCoach]

I would recommend that you use the query designer view.

Add the actions table and the users table twice. The second copy will be named
users_1.

Now you can make your two joins.

[svcghost]

Coach, thank you so much! Worked just perfectly.

[RuralGuy]

Coach, thank you so much! Worked just perfectly.

Are you ready to follow the link in my sig and mark this thread as Solved?

[HiTechCoach]

Coach, thank you so much! Worked just perfectly.

You're welcome!

Glad that worked for you.

Please mark this as solved.

[svcghost]

Are you ready to follow the link in my sig and mark this thread as Solved?

done. thanks.

[RuralGuy]

Thank you and did you give Boyd a point or two?

[svcghost]

I added to his reputation, approved. If that's what you meant?

[RuralGuy]

I added to his reputation, approved. If that's what you meant?

That's what I meant and I know Boyd deserves it. Thanks.

[HiTechCoach]

Thank you svcghost and RuralGuy!