CODEWITHSUNDEEP
sql-serverstored-proceduresinner-join
RelatedRhymes
RelatedRhymes

Reputation: 428

Stored Procedure to display two set of results using joins

Lets say I have two tables

  Create table tblMessages
  (
  MessageId int primary key identity,
  Title nvarchar(max),
  Body nvarchar(max),
  MsgFrom nvarchar(10),
  MsgTo nvarchar(10),
  SentDateTime datetime,
  MsgRead int default 0
  )

and 

  Create table tblAllUsers
  ( 
   Id int primary key identity,
   UserId nvarchar(100,
   FullName nvarchar(50)
  )

and here is a stored procedure that i tried display my messages for the logged in user

  Alter proc spExtractInbox
  @UserId nvarchar(10)
  as
  begin
  Select * from tblMessages 
  INNER JOIN
  tblAllUsers ON tblMessages.MsgTo = tblAllUsers.UserId
  where tblMessages.MsgTo=@UserId order by tblMessages.SentDateTime desc
  end

The above query works just fine to display message and UserId of the Sender(MsgFrom) but i want to display the name of the Sender which is stored in tblAllUsers.I am using this in a grdiview.I am not sure if i am explaining it the best way.

The problem is join is already joining the two tables depending on tblMessages.MsgTo = tblAllUsers.UserId. So how is it possible for me to do tblMessages.MsgFrom = tblAllUsers.UserId to get the Senders name?

Upvotes: 0

Views: 31

Answers (2)

Julien Vavasseur
Julien Vavasseur

Reputation: 3952

You must use Alias (AS aliasname) in order to double join on the same table:

Select ... from tblMessages AS msg 
INNER JOIN tblAllUsers AS ut
    ON msg.MsgTo = ut.UserId
INNER JOIN tblAllUsers AS uf
    ON msg.MsgFrom = uf.UserId
where msg.MsgTo=@UserId or msg.MsgFrom=@UserId
order by tblMessages.SentDateTime desc

Alias also make your life easier because you don't have to repeat the full table name each time. See Using Table Aliases

Add the columns you need to the select (msg.Title, ut.Fullname, ...)

Upvotes: 1

xQbert
xQbert

Reputation: 35323

You need to join to allUsers twice once for the sender once for the receiver...

  SELECT * 
  FROM tblMessages 
  INNER JOIN tblAllUsers Rec
    ON tblMessages.MsgTo = Rec.UserId
  INNER JOIN tblAllusers Snd
    on tblMessages.MsgFrom = Rec.userID
  WHERE Rec.MsgTo=@UserId 
  ORDER BY tblMessages.SentDateTime desc

Limit * to only needed fields. Notice the second join to tblAllusers with alias used.

Upvotes: 0

Related Questions