sql server programming: Query -- sql server programming

Query

NR
11/29/2004 11:25:02 PM

sql server programming: Hi there,

Need to know how can we write single query for the example below;

Table1 Name: Users
UserID + UserName
----------------------------------------------
u1 | User - 1
u2 | User - 2

Table2 Name: Program
ProgramID + Program Name + Teacher1 + Teacher2
--------------------------------------------------------------------
Pg1 | Visual Basic | u2 | u1
Pg2 | MS Access | u1 | u2

I need to reterive records from Table "Program" along with User Name. Please
see the desired result below;

ProgramID + Program Name + Teacher1 + Teacher2
--------------------------------------------------------------------
Pg1 | Visual Basic | User - 2 | User - 1
Pg2 | MS Access | User - 1 | User - 2

How can i write a single SQL Statement to get above results.

Will be thankful for any help.

Regards,

RE: Query

John Bell
11/29/2004 11:55:02 PM

Hi

Posting DDL and example data is a better way to post than psudo table
structures see: http://www.aspfaq.com/etiquette.asp?id=5006

Try:
SELECT P.ProgramID, P.[Program Name], U.[UserName] AS Teacher1,
U2.[UserName] AS Teacher2
FROM Program P
JOIN Users U1 ON U1.UserId = P.Teacher1
JOIN Users U2 ON U2.UserId = P.Teacher2

If Teacher1 and Teacher2 are nullable then you may want to user left joins.

John

[quoted text, click to view]

Re: Query

ijustok
11/30/2004 3:44:13 PM

select a.ProgramID ,a.[Program Name],b.UserName as Teacher1,c.UserName as
Teacher2
from Program a
inner join Users b on a.Teacher1=b.UserID
inner join Users c on a.Teacher2=c.UserID

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server programming group.