用于检索友谊的 MYSQL 查询只能以一种方式工作？

Question

我有一个查询应该检索用户的 friend 。

鉴于我们是用户二并且我们发送请求给用户一，查询应该返回的帐户信息>用户一。 ( SQLfiddle of this scenario ) [工作正常]

鉴于我们是用户一并且我们收到来自用户二的请求，查询应该返回的帐户信息>用户二。 ( SQLfiddle of this scenario ) [返回空结果]

所以只要我们是发送请求的用户，查询就可以正常工作，但是如果我们是接收请求的用户，它就不起作用。

我可以通过切换连接来解决这个问题，LEFT => RIGHT，RIGHT => LEFT。但这打破了第一种情况。
我可以修复我对两个查询的联合。 ( SQLfiddle of this scenario ) 但肯定不是最有效的方法。那么，基本问题:我应该如何创建此查询？

场景一:

SELECT `accounts`.*,
  IFNULL(`f1`.`sender`,        `f2`.`sender`)        AS `sender`,
  IFNULL(`f1`.`recipient`,     `f2`.`recipient`)     AS `recipient`,
  IFNULL(`f1`.`date_sent`,     `f2`.`date_sent`)     AS `date_sent`,
  IFNULL(`f1`.`date_reviewed`, `f2`.`date_reviewed`) AS `date_reviewed`,
  IFNULL(`f1`.`accepted`,      `f2`.`accepted`)      AS `accepted` 
FROM `accounts` 
  LEFT  JOIN `friendships` AS `f1` ON `accounts`.`id` = `f1`.`sender` 
  RIGHT JOIN `friendships` AS `f2` ON `accounts`.`id` = `f2`.`recipient` 
WHERE (`f1`.`recipient` = 2 OR `f2`.`sender` = 2);

补丁场景二

SELECT `accounts`.*,
  IFNULL(`f1`.`sender`,        `f2`.`sender`)        AS `sender`,
  IFNULL(`f1`.`recipient`,     `f2`.`recipient`)     AS `recipient`,
  IFNULL(`f1`.`date_sent`,     `f2`.`date_sent`)     AS `date_sent`,
  IFNULL(`f1`.`date_reviewed`, `f2`.`date_reviewed`) AS `date_reviewed`,
  IFNULL(`f1`.`accepted`,      `f2`.`accepted`)      AS `accepted` 
FROM `accounts` 
  RIGHT JOIN `friendships` AS `f1` ON `accounts`.`id` = `f1`.`sender` 
  LEFT  JOIN `friendships` AS `f2` ON `accounts`.`id` = `f2`.`recipient` 
WHERE (`f1`.`recipient` = 2 OR `f2`.`sender` = 2);

适用于两种情况(问题基本上是；没有工会我怎么能做到这一点？):

SELECT `accounts`.*,
  IFNULL(`f1`.`sender`,        `f2`.`sender`)        AS `sender`,
  IFNULL(`f1`.`recipient`,     `f2`.`recipient`)     AS `recipient`,
  IFNULL(`f1`.`date_sent`,     `f2`.`date_sent`)     AS `date_sent`,
  IFNULL(`f1`.`date_reviewed`, `f2`.`date_reviewed`) AS `date_reviewed`,
  IFNULL(`f1`.`accepted`,      `f2`.`accepted`)      AS `accepted` 
FROM `accounts` 
  LEFT  JOIN `friendships` AS `f1` ON `accounts`.`id` = `f1`.`sender` 
  RIGHT JOIN `friendships` AS `f2` ON `accounts`.`id` = `f2`.`recipient` 
WHERE (`f1`.`recipient` = 1 OR `f2`.`sender` = 1)
UNION
SELECT `accounts`.*,
  IFNULL(`f1`.`sender`,        `f2`.`sender`)        AS `sender`,
  IFNULL(`f1`.`recipient`,     `f2`.`recipient`)     AS `recipient`,
  IFNULL(`f1`.`date_sent`,     `f2`.`date_sent`)     AS `date_sent`,
  IFNULL(`f1`.`date_reviewed`, `f2`.`date_reviewed`) AS `date_reviewed`,
  IFNULL(`f1`.`accepted`,      `f2`.`accepted`)      AS `accepted` 
FROM `accounts` 
  RIGHT  JOIN `friendships` AS `f1` ON `accounts`.`id` = `f1`.`sender` 
  LEFT JOIN `friendships` AS `f2` ON `accounts`.`id` = `f2`.`recipient` 
WHERE (`f1`.`recipient` = 1 OR `f2`.`sender` = 1);

架构创建:

CREATE TABLE `accounts` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `status` tinyint(4) DEFAULT '0',
  `type` varchar(25) NOT NULL,
  `username` varchar(25) NOT NULL,
  `first_name` varchar(80) NOT NULL,
  `last_name` varchar(80) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;

/*Data for the table `accounts` */

insert  into `accounts`
(`id`,`status`,`type`,`username`,`first_name`,`last_name`) 
values 
(1,1,'writer','user1','User','One'),
(2,1,'writer','user2','User','Two'),
(3,1,'publisher','user3','User','Three');

CREATE TABLE `friendships` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `sender` int(11) NOT NULL,
  `recipient` int(11) NOT NULL,
  `date_sent` datetime DEFAULT NULL,
  `date_reviewed` datetime DEFAULT NULL,
  `accepted` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;

/*Data for the table `friendships` */

insert  into `friendships`
(`id`,`sender`,`recipient`,`date_sent`,`date_reviewed`,`accepted`) 
values (3,2,1,'2013-07-16 20:54:46',NULL,NULL);

Answer 1

请试试这个query .我删除了两个连接。并且只需要一个连接，双方都可以工作。

SELECT `accounts`.*, `f1`.`sender`  AS `sender`,`f1`.`recipient`  AS `recipient`,`f1`.`date_sent`  AS `date_sent`,  `f1`.`date_reviewed` AS `date_reviewed`,`f1`.`accepted`    AS `accepted` FROM `accounts`   JOIN `friendships` AS `f1` ON (`accounts`.`id` = `f1`.`sender`   or `accounts`.`id` = `f1`.`recipient`) WHERE    if(`f1`.`recipient` = 1, `f1`.`sender` , if(`f1`.`sender` = 1, `f1`.`recipient` , 0)) = `accounts`.`id`;