This solution is a total stab in the dark as I don't know what your project is trying to do.
CREATE TABLE dbo.Users (
UserId INT IDENTITY NOT NULL,
Username VARCHAR(50) NOT NULL,
[Password] VARCHAR(50) NOT NULL,
NickName VARCHAR(50) NULL,
FirstName VARCHAR(50) NULL,
LastName VARCHAR(50) NULL,
[Date] DATE NULL,
City VARCHAR(50) NULL,
[State] VARCHAR(10) NULL,
CONSTRAINT PK_Users PRIMARY KEY(UserId)
)
CREATE TABLE dbo.[Messages] (
MessageId INT IDENTITY NOT NULL,
[DateTime] DATETIME NOT NULL,
FromUserId INT NOT NULL,
ToUserId INT NOT NULL,
[Message] VARCHAR(MAX) NOT NULL,
[Status] VARCHAR(50) NOT NULL,
CONSTRAINT PK_Messages PRIMARY KEY (MessageId),
CONSTRAINT FK_Messages_Users_FromUserId FOREIGN KEY (FromUserId) REFERENCES dbo.Users(UserId),
CONSTRAINT FK_Messages_Users_ToUserId FOREIGN KEY (ToUserId) REFERENCES dbo.Users(UserId)
)
SELECT *
FROM
(
SELECT
*
FROM dbo.Users
INNER JOIN dbo.[Messages]
ON Users.UserId = [Messages].ToUserId
UNION
SELECT
*
FROM dbo.Users
INNER JOIN dbo.[Messages]
ON Users.UserId = [Messages].FromUserId
) AS AllMessages
WHERE
UserId = @userId
I highly recommend taking a look at this presentation on the Microsoft Virtual Academy web site to learn about database design:
http://www.microsoftvirtualacademy.com/training-courses/database-fundamentals[
^]