Comments database issue

[wayneww]

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)

[eben_ezer32]

what can i do to to create own webpage?

[sknake]

what can i do to to create own webpage?

eben_ezer: You should create your own thread to ask questions, not ask on someone elses thread. Check out the ASP.NET forum.

wayneww: This is really an SQL question. In the future please ask query questions in the MSSQL forum.

To answer your question you could use subqueries to pull back the username information:

SELECT  Messages.MessageID, Messages.M_DateTime, Messages.M_Subject, Messages.M_Message, 
Messages.PictureID, Messages.UserIdSender, Messages.M_ReadYet, Messages.UserIdRecipient,
(
  Select Top 1 aspnet_Users.UserName
  From aspnet_Users (NOLOCK)
  Where aspnet_Users.UserId = Messages.UserIdSender
) As UserNameSender,
(
  Select Top 1 aspnet_Users.UserName
  From aspnet_Users (NOLOCK)
  Where aspnet_Users.UserId = Messages.UserIdRecipient
) As UserNameRecipient
FROM Messages 
WHERE (Messages.UserIdSender = @UserId) OR (Messages.UserIdRecipient = @UserId)

[mathewmoozh]

Hi,Iam using a variable c and in a specific situation i need to decrease the valueusing as c--,but this when used it was commented:-O,then i used c=c-1.Can any one tell me how can i use the earlier way without as comment,any help will be greatly appreciated,Thanks in advance,Plz help me if anyone can

[Ezzaral]

What language are you using? You hijacked someone else's question about a database and I don't even know where to move this because you haven't given any context.