Help with a SELECT Query

Foreign Key Fun

[Argblat]

Sometimes it's fun to realize just how stupid you actually are...

I have a table with User information (id and name)

USER [userID, userName]
-----------------------
1 ¦ Bob
2 ¦ Jim
3 ¦ Ace

I have another table where the userID is used as a FK more than once

PROJECT [projID, pSponsor, pKeyContact]
---------------------------------------
1 ¦ 3 ¦ 1
2 ¦ 2 ¦ 1

So for Project #1, Ace is the Sponsor, Bob is Key Contact

For Project #2, Jim is the Sponsor, Bob is the Key Contact

The thing I can't figure out, is how to write a query that displays the PROJECT table but replaces the FK id # with userName from the user table

so the finished product would look like

QUERY RESULTS
------------------------
1 ¦ Ace ¦ Bob
2 ¦ Jim ¦ Bob

Any help is greatly appreciated as I'm feeling slightly moronic right now for now knowing how to do this

-Mike

[coopster]

You JOIN to the table twice with an ALIAS.

SELECT 
  projID, 
  sponsor.userName AS sponsorName, 
  contact.userName AS contactName 
 FROM PROJECT 
 INNER JOIN USER AS sponsor ON (pSponsor = sponsor.userID) 
 INNER JOIN USER AS contact ON (pKeyContact = contact.userID) 
;

[syber]

It is easier to visualize if you imagine that you have two user tables, one that list sponsors and another that list contacts. By aliasing the USER table, SQL will treat it as two separate tables.

[Argblat]

coopster and syber,

Thank you for your help. That certainly solves the problem that I had asked.

(of course there is always a however)

However,
What do I do if one of the columns is optional (lets say Key Contact for the sake of the example). Now, if Key Contact is null but Sponsor has a value, my query won't return that row.

Again, I appreciate the first round of help and realize that this is basically a completely different question.

-Mike

[syber]

You would need to do a LEFT OUTER J0IN instead of an INNER JOIN.

This will force the project row to return even if there is not a match against sponsor or contact.

[Argblat]

You sir, are a genious

and I need to brush up on my JOIN Statements

Thank you kindly,
Mike

[coopster]

Message number 11 has some explanation regarding LEFT JOIN versus INNER JOIN [webmasterworld.com]