Multiple FKs of the same PK problem!

Hi SQL folks,

I have a fuzzy problem with my application,

I'm building a small internal messaging system. I have two tables: SysUsers and mail tables.

I need to select both the "mailFrom" and "mailTo" from the mail table - both of them is a uniqueidenifier and are FK from the UserId in the SysUsers table- , and then get the correspoding UserName from the SysUsers table, how can I do that?

Here is my query:

/*Get the UserName of both the Sender and Reciever*/
SELECT mail.messageId, mail.messageSubject, mail.mailFrom, mail.mailTo, SysUsers.UserName
FROM mail INNER JOIN
SysUsers ON mail.mailTo= SysUsers.UserId
AND mail.mailFrom= SysUsers.UserId

/*Get only the current users mails*/
WHERE (SysUsers.UserId = @.UserId)

Hello Jocker,

You have to define two inner join to the same table with different aliasses.

SELECT ... , MailTo.UserName, MailFrom.UserName

FROM mail

INNER JOIN SysUsers AS MailTo ON MailTo.UserId = mail.mailTo

INNER JOIN SysUsers AS MailFrom ON MailFrom.UserId = mail.mailFrom

WHERE (MailTo.UserId = @.UserId OR MailFrom.UserId = @.UserId)

|||

You need to alias the Sysusers Table

SELECT mail.messageId, mail.messageSubject, mail.mailFrom, mail.mailTo,
S1.UserName AS UserNameTo,S2.UserName AS UserNameFrom
FROM mail
INNER JOIN SysUsers S1 ON mail.mailTo = S1.UserId
INNER JOIN SysUsers S2 ON mail.mailFrom = S2.UserId

WHERE (S1.UserId = @.UserId)

|||

jeroenm:

Hello Jocker,

You have to define two inner join to the same table with different aliasses.

SELECT ... , MailTo.UserName, MailFrom.UserName

FROM mail

INNER JOIN SysUsers AS MailTo ON MailTo.UserId = mail.mailTo

INNER JOIN SysUsers AS MailFrom ON MailFrom.UserId = mail.mailFrom

WHERE (MailTo.UserId = @.UserId OR MailFrom.UserId = @.UserId)

Hi jepenm,

Thanks for you responce :)

|||

TATWORTH:

You need to alias the Sysusers Table

SELECT mail.messageId, mail.messageSubject, mail.mailFrom, mail.mailTo,
S1.UserName AS UserNameTo,S2.UserName AS UserNameFrom
FROM mail
INNER JOIN SysUsers S1 ON mail.mailTo = S1.UserId
INNER JOIN SysUsers S2 ON mail.mailFrom = S2.UserId

WHERE (S1.UserId = @.UserId)

Hi TATWORTH,

Thanks for your response