MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE `users` (
  `user_id` int(11) NOT NULL,
  `firstName` varchar(50) NOT NULL,
  `lastName` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `users` 
(`user_id`, `firstName`, `lastName`) 
VALUES
(18, 'Bart', 'Simpson'),
(33, 'Marge', 'Simpson'),
(45, 'Homer', 'Simpson'),
(99, 'Lisa', 'Simpson');

CREATE TABLE `messages` (
  `id` int(11) NOT NULL,
  `idFrom` int(11) DEFAULT NULL,
  `idTo` int(11) DEFAULT NULL,
  `message` varchar(500) DEFAULT NULL,
  `messageDate` datetime NOT NULL 
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Indexes for table `messages`
--
ALTER TABLE `messages`
  ADD PRIMARY KEY (`id`);

--
-- AUTO_INCREMENT for table `messages`
--
ALTER TABLE `messages`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=34;
COMMIT;


INSERT INTO messages
 (idFrom, idTo, message, messageDate)
 VALUES 
 (18, 33, 'Hello Marge', '2000-05-05 16:29:25'),
 (33, 18, 'Hello Bart', '2001-05-05 16:29:25'),
 (18, 99, 'Hello Lisa', '2002-05-05 16:29:25'),
 (99, 45, 'Hello Homer', '2003-05-05 16:29:25'),
 (18, 99, 'Hello Lisa Again!!!', '2004-05-05 16:29:25'),
 (33, 18, 'Hello Bart Again', '2005-05-05 16:29:25');

select version();



version()


8.0.24


SELECT * FROM users;


user_id
firstName
lastName


18
Bart
Simpson

33
Marge
Simpson

45
Homer
Simpson

99
Lisa
Simpson


SELECT * FROM messages


id
idFrom
idTo
message
messageDate


34
18
33
Hello Marge
2000-05-05 16:29:25

35
33
18
Hello Bart
2001-05-05 16:29:25

36
18
99
Hello Lisa
2002-05-05 16:29:25

37
99
45
Hello Homer
2003-05-05 16:29:25

38
18
99
Hello Lisa Again!!!
2004-05-05 16:29:25

39
33
18
Hello Bart Again
2005-05-05 16:29:25


SELECT * FROM
 (
 SELECT *,
     CASE
        WHEN idFrom = 18 THEN idTo
        WHEN idTo = 18 THEN idFrom
        ELSE ''
    END AS contact
 FROM messages 
 WHERE idFrom = 18 OR idTo = 18
 ) query1
INNER JOIN users ON users.user_id = query1.contact



id
idFrom
idTo
message
messageDate
contact
user_id
firstName
lastName


39
33
18
Hello Bart Again
2005-05-05 16:29:25
33
33
Marge
Simpson

35
33
18
Hello Bart
2001-05-05 16:29:25
33
33
Marge
Simpson

34
18
33
Hello Marge
2000-05-05 16:29:25
33
33
Marge
Simpson

38
18
99
Hello Lisa Again!!!
2004-05-05 16:29:25
99
99
Lisa
Simpson

36
18
99
Hello Lisa
2002-05-05 16:29:25
99
99
Lisa
Simpson

id	idFrom	idTo	message	messageDate
34	18	33	Hello Marge	2000-05-05 16:29:25
35	33	18	Hello Bart	2001-05-05 16:29:25
36	18	99	Hello Lisa	2002-05-05 16:29:25
37	99	45	Hello Homer	2003-05-05 16:29:25
38	18	99	Hello Lisa Again!!!	2004-05-05 16:29:25
39	33	18	Hello Bart Again	2005-05-05 16:29:25

id	idFrom	idTo	message	messageDate	contact	user_id	firstName	lastName
39	33	18	Hello Bart Again	2005-05-05 16:29:25	33	33	Marge	Simpson
35	33	18	Hello Bart	2001-05-05 16:29:25	33	33	Marge	Simpson
34	18	33	Hello Marge	2000-05-05 16:29:25	33	33	Marge	Simpson
38	18	99	Hello Lisa Again!!!	2004-05-05 16:29:25	99	99	Lisa	Simpson
36	18	99	Hello Lisa	2002-05-05 16:29:25	99	99	Lisa	Simpson