sql server programming: Returning 2 values from the same table -- sql server programming

Returning 2 values from the same table

nsajus NO[at]SPAM yahoo.com
11/2/2005 8:19:51 PM

sql server programming: Hi,

I have a strange problem.

I have 2 tables

Table1 called tblUser contains columns UserID, FirstName, LstName

Table2 called tblMemos contains columns MemoID, FromUserID, ToUserID,
MemoNote.

The FromUserID and ToUserID are kind of like foreign keys to UserID in
tblUsers

I want to write a stored procedure which will return a list of MemoID,
FromUserName, ToUserName, MemoNote

How do I go about doing that since both FromUserID and ToUserID in
tblMemos refernce the UserID in tblUSer?

I don't know if the following statements in a stored procedure will
return the MemoID, FromUSerName, LstUserName and MemoNotes.

SELECT tblUser.FirstName + ' ' + tblSecurityGrouping.LastName
AS FromUserName
FROM tblUser
INNER JOIN tblMemos
ON tblUser.UserID = tblMemos.FromUserID

SELECT tblMemoID, tblUser.FirstName + ' ' +
tblSecurityGrouping.LastName
AS ToUserName ,
tblMemo.MemoNote
FROM tblUser
INNER JOIN tblMemos
ON tblUser.UserID = tblMemos.ToUserID

Can some one guide me on how I can achieve what i want? Any help will
be greatly appreciated.

Thanks,

Ann

RE: Returning 2 values from the same table

kannan
11/2/2005 8:37:02 PM

Hi Ann
You have to use Allise of tblUser and join this table.

select tblMemo.MemoId, tbl1.FromUserName, tbl2.ToUserName
From tblMemo Inner Join tblUser tbl1 On tblMemo.FromUserID = tbl1.UserId
Inner Join tblUser tbl2 On tbl2.UserId = tblMemo.ToUserId

I think this will work...

--
kannan

[quoted text, click to view]

RE: Returning 2 values from the same table

R.D
11/2/2005 8:42:06 PM

Try this. Untested

SELECT a.*, b.*
FROM
(SELECT tblUser.UserID as UserID1 , tblUser.FirstName + tblUser.LstName as
fromName from
tblUser inner join tblMemos on tblMemos. FromUserID = tblUser.UserID) a
FULL OUTER JOIN
(SELECT UserID, .FirstName + LstName as ToName from
tblUser inner join tblMemos on tblMemos. ToUserID = UserID) b
on a.UserID1 =b.UserID

--
Regards
R.D
--Knowledge gets doubled when shared

[quoted text, click to view]

Re: Returning 2 values from the same table

nsajus NO[at]SPAM yahoo.com
11/3/2005 6:38:50 AM

Thanks Kannan and R.D. It worked ! Really appreciate your help..
Ann

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.