friend-messages-join.sql

CREATE TABLE IF NOT EXISTS `users` (
  `id` int(6) unsigned NOT NULL,
  `name` varchar(200) NOT NULL,
  PRIMARY KEY (`id`)
) DEFAULT CHARSET=utf8;
INSERT INTO `users` (`id`, `name`) VALUES
  ('1', 'Marceline'),
  ('2', 'Finn'),
  ('3', 'Jake');

CREATE TABLE IF NOT EXISTS `messages` (
  `id` int(6) unsigned NOT NULL,
  `user` int(6) unsigned NOT NULL,
  `when` datetime NOT NULL,
  `text` varchar(200) NOT NULL,
  PRIMARY KEY (`id`)
) DEFAULT CHARSET=utf8;
INSERT INTO `messages` (`id`, `user`, `when`, `text`) VALUES
  ('1', '1', now(), 'I\'m not mean. I\'m a thousand years old, and I just lost track of my moral code.'),
  ('2', '2', now(), 'That doesn\'t fair well for their... grade point average.'),
  ('3', '3', now(), 'Dude, sucking at sumthin’ is the first step towards being sorta good at something.');
  
CREATE TABLE IF NOT EXISTS `friends` (
  `from` int(6) unsigned NOT NULL,
  `to` int(6) unsigned NOT NULL,
  PRIMARY KEY (`from`, `to`)
) DEFAULT CHARSET=utf8;
INSERT INTO `friends` (`from`, `to`) VALUES
  ('1', '2'),
  ('1', '3');
  
SELECT
  m.id id,
  m.user `user`,
  u.name name,
  m.text `text`,
  m.when `when`
FROM messages m
INNER JOIN (
  SELECT `from`, `to`
  FROM `friends`
  WHERE `from`=1
) f
ON m.user = f.to
INNER JOIN users u on m.user = u.id
ORDER BY m.when DESC;