By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE [dbo].[Message](
[message_id] [int] NOT NULL,
[messagetext] [nvarchar](max) NOT NULL,
[datesent] [date] NOT NULL,
[profile_idtoo] [int] NOT NULL,
[profile_idfrom] [int] NOT NULL
)
CREATE TABLE [dbo].[Profile](
[profile_id] [int] NOT NULL,
[profilename] [nvarchar](255) NOT NULL,
[regdate] [date] NOT NULL,
[user_id] [int] NOT NULL
)
INSERT [dbo].[Message] ([message_id], [messagetext], [datesent], [profile_idtoo], [profile_idfrom]) VALUES (1, N'This playlist is the best', CAST(N'2018-11-13' AS Date), 9, 8)
INSERT [dbo].[Message] ([message_id], [messagetext], [datesent], [profile_idtoo], [profile_idfrom]) VALUES (2, N'I got superscared by a few of those moviews tbh', CAST(N'2017-12-14' AS Date), 2, 3)
INSERT [dbo].[Message] ([message_id], [messagetext], [datesent], [profile_idtoo], [profile_idfrom]) VALUES (3, N'I think you should look atmy playlist called "the best songs of all time". ', CAST(N'2017-09-14' AS Date), 2, 5)
INSERT [dbo].[Message] ([message_id], [messagetext], [datesent], [profile_idtoo], [profile_idfrom]) VALUES (4, N'Best thing I ever done signin up to this site!', CAST(N'2018-11-13' AS Date), 6, 7)
INSERT [dbo].[Profile] ([profile_id], [profilename], [regdate], [user_id]) VALUES (1, N'TheSpazzCommander', CAST(N'2017-02-01' AS Date), 1)
INSERT [dbo].[Profile] ([profile_id], [profilename], [regdate], [user_id]) VALUES (2, N'CaptainBuzzkill', CAST(N'2015-01-01' AS Date), 1)
INSERT [dbo].[Profile] ([profile_id], [profilename], [regdate], [user_id]) VALUES (3, N'EVLM', CAST(N'2018-08-15' AS Date), 2)
INSERT [dbo].[Profile] ([profile_id], [profilename], [regdate], [user_id]) VALUES (4, N'JBlunt', CAST(N'2017-07-15' AS Date), 3)
INSERT [dbo].[Profile] ([profile_id], [profilename], [regdate], [user_id]) VALUES (5, N'JaneHeart', CAST(N'2015-05-01' AS Date), 4)
INSERT [dbo].[Profile] ([profile_id], [profilename], [regdate], [user_id]) VALUES (6, N'JimmyHeart', CAST(N'2015-01-05' AS Date), 4)
INSERT [dbo].[Profile] ([profile_id], [profilename], [regdate], [user_id]) VALUES (7, N'Khall', CAST(N'2014-01-01' AS Date), 6)
INSERT [dbo].[Profile] ([profile_id], [profilename], [regdate], [user_id]) VALUES (8, N'thehunter', CAST(N'2017-01-01' AS Date), 7)
INSERT [dbo].[Profile] ([profile_id], [profilename], [regdate], [user_id]) VALUES (9, N'thehunterswife', CAST(N'2017-01-01' AS Date), 7)
13 rows affected
SELECT Profile.profile_id,Profile.profilename as Recipient, m1.profilename,
Message.messagetext as MessageText,
message.datesent as DateSent
FROM Profile
INNER JOIN
Message ON Profile.profile_id = Message.profile_idtoo
inner join (SELECT profile_id,profilename from Profile
Inner JOIN
Message on Profile.profile_id = Message.profile_idfrom
WHERE Message.profile_idfrom = 3) m1 on Message.profile_idfrom = m1.profile_id
WHERE Message.profile_idfrom = 3
| profile_id | Recipient | profilename | MessageText | DateSent |
|---|---|---|---|---|
| 2 | CaptainBuzzkill | EVLM | I got superscared by a few of those moviews tbh | 14/12/2017 00:00:00 |
SELECT profile_id,profilename from Profile
Inner JOIN
Message on Profile.profile_id = Message.profile_idfrom
WHERE Message.profile_idfrom = 3
| profile_id | profilename |
|---|---|
| 3 | EVLM |