José Ferreira José Ferreira - 23 days ago 4
SQL Question

How to "join" a table twice with just one join?

It's hard for me to explain this one so you're likely confused by the title.
But I'll try my best here.

Say we have the following scenario:

Tests Table


  • Test ID

  • testtakenby(id)

  • testrevisedby(id)



Users




  • User ID

  • Username



Is there a way for me to be able to get the following result:

"Test was taken by user1 and revised by user2"

Without having to join the tables twice?
Like this:

select * from tests
inner join users on tests.testtakenby = users.userid
inner join users on tests.testrevisedby = users.userid


I'm hoping there is an advanced SQL trick of getting it done as I worry of possible performance issues by joining the same table twice (I presume it wouldn't perform as well with a double join)

Answer

Two joins should perform just fine and there is no alternate double join syntax

Comments