Hi all, I'm using ASP.NET programming with SQL Express 2008:
Basically to simplify and summarise, I have Users who can message to each other:
Users table: UserID (PK); UserName
Messages table: MessageID (PK); UserIDSender; UserIDRecipient
I want to Select * from Messages table and also the UserName for the UserIDSender and UserID Recipient - i.e. I want the results to show MessageID, UserIDSender, UserName (of UserIDSender), UserName (of UserIDRecipient) of only those where either the Sender or Recipient is of the current User... My code below does half the job but does not show the UserName of the Sender...
I've tried using another INNER JOIN added but it comes out with duplicate results while also tried a nested Select statement but that didn't work...
Can someone please help??? Or perhaps I can design my database better? many thx in advance.
SELECT Messages.MessageID, Messages.M_DateTime, Messages.M_Subject, Messages.M_Message, Messages.PictureID, Messages.UserIdSender, Messages.M_ReadYet, Messages.UserIdRecipient, aspnet_Users.UserId, aspnet_Users.UserName FROM Messages INNER JOIN aspnet_Users ON Messages.UserIdRecipient = aspnet_Users.UserId WHERE (Messages.UserIdSender = @UserId) OR (Messages.UserIdRecipient = @UserId)