sql - 向sql查询添加额外字段

Question

我有一个为私有(private)消息系统编写的查询，但我想在查询中添加一个 extar 字段，但不确定如何操作。

目前，它获取发件人的用户名，但不获取收件人的用户名，但它确实获取发件人/收件人的两个 ID。

如何在查询中添加额外字段以获取收件人的用户名？我考虑过嵌套选择。

这是我当前的查询

SELECT p.*, u.username as sender, DATE_FORMAT(u.signup_date, '%b %Y') as joindate, m.avatar FROM privatemsgrec p INNER JOIN users u ON p.senderid=u.userid INNER JOIN misc m ON m.userid=u.userid WHERE p.messageid='1' LIMIT 1

这是表结构

CREATE TABLE IF NOT EXISTS `misc` (
  `miscid` int(4) NOT NULL auto_increment,
  `userid` int(4) NOT NULL default '0',
  `profpic` varchar(100) NOT NULL default '',
  `avatar` varchar(100) NOT NULL default '',
  `signature` text NOT NULL,
  `alerts` enum('y','n') NOT NULL default 'y',
  PRIMARY KEY  (`miscid`)
)

CREATE TABLE IF NOT EXISTS `privatemsgsent` (
  `messageid` int(10) NOT NULL auto_increment,
  `senderid` int(4) NOT NULL default '0',
  `recipientid` int(4) NOT NULL default '0',
  `subject` varchar(255) NOT NULL default '',
  `message` text NOT NULL,
  `datesent` date NOT NULL default '0000-00-00',
  PRIMARY KEY  (`messageid`)
)

CREATE TABLE IF NOT EXISTS `users` (
  `userid` int(25) NOT NULL auto_increment,
  `first_name` varchar(25) NOT NULL default '',
  `email` varchar(255) NOT NULL default '',
  `username` varchar(25) NOT NULL default '',
  `password` varchar(32) NOT NULL default '',
  `salt` char(3) NOT NULL default '',
  `sex` varchar(6) NOT NULL default '',
  `user_level` enum('0','1','2','3') NOT NULL default '0',
  `signup_date` datetime NOT NULL default '0000-00-00 00:00:00',
  `last_login` datetime NOT NULL default '0000-00-00 00:00:00',
  `activated` enum('0','1') NOT NULL default '0',
  PRIMARY KEY  (`userid`)
)

Answer 1

SELECT p.*, 
    us.username as sender, ur.username as recipient, 
    DATE_FORMAT(u.signup_date, '%b %Y') as joindate, m.avatar
FROM privatemsgrec p 
INNER JOIN users us ON p.senderid = us.userid 
INNER JOIN users ur ON p.recipientid = ur.userid 
INNER JOIN misc m ON m.userid = u.userid 
WHERE p.messageid = '1' 
LIMIT 1