在以下问题中，我很难得到任何结果或正确的结果-http://www.sqlfiddle.com/#!9/696第2/4版
总的目标是列出所有的交易用户谁是联系在一起的'客户'。因此，如果john正在查看他的 Jmeter 盘，他将看到alice（他的客户）租了哪些书（包括书名），卖了哪些书（他将看不到那本书的书名）。
当两个多表连接到父表时，其中两个依赖表的每一行都设置了“active”标志，我似乎不能只获得活动行。

# USERS

CREATE TABLE `users` (
  `id` int(11) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `active` boolean DEFAULT NULL
);

INSERT INTO `users` (`id`, `name`, `active`) VALUES
(1, 'John', 1),
(2, 'Alice', 1),
(3, 'Jess', 1),
(4, 'Bob', 1);

# BOOKS

CREATE TABLE `books` (
  `id` int(11) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `active` boolean DEFAULT NULL
);

INSERT INTO `books` (`id`, `name`, `active`) VALUES
(1, 'On the Road', 1),
(2, 'Neuromancer', 0),
(3, 'Modern History', 1),
(4, 'Red Mars', 1);

# TRANSACTIONS

CREATE TABLE `transactions` (
  `id` int(11) NOT NULL,
  `user_1_id` int(11) NOT NULL,
  `user_2_id` int(11) DEFAULT NULL,
  `book_id` int(11) DEFAULT NULL,
  `timestamp` varchar(20) DEFAULT NULL,
  `type` enum('Rent', 'Sold') NOT NULL
);

INSERT INTO `transactions` (`id`, `user_1_id`, `user_2_id`, `book_id`,     `timestamp`, `type`) VALUES
(1, 1, 2, 1, '1465238591', 'Rent'),
(2, 2, 1, 2, '1465238592', 'Rent'),
(3, 2, 3, 3, '1465238593', 'Rent'),
(4, 3, 4, NULL, '1465238594', 'Sold'),
(5, 2, 3, NULL, '1465238595', 'Sold'),
(6, 3, 4, NULL, '1465238596', 'Sold'),
(7, 2, 2, 4, '1465238597', 'Rent'),
(8, 1, 3, 1, '1465238598', 'Rent'),
(9, 2, 4, 2, '1465238598', 'Rent');

# RELATIONSHIPS

CREATE TABLE `relationships` (
  `id` int(11) NOT NULL,
  `user_1_id` int(11) DEFAULT NULL,
  `user_2_id` int(11) NOT NULL,
  `type` enum('Customer', 'Supplier') NOT NULL
);

INSERT INTO `relationships` (`id`, `user_1_id`, `user_2_id`, `type`) VALUES
(1, 1, 2, 'Customer'),
(2, 2, 1, 'Customer'),
(3, 2, 4, 'Customer'),
(3, 1, 4, 'Supplier'),
(3, 3, 1, 'Customer');

查询：

SELECT DISTINCT 
  t.id,
  t.type,
  t.timestamp,
  u1.name as user_1_name,
  u2.name as user_2_name,
  b.name as book_name
  FROM transactions t

  LEFT JOIN relationships r ON (r.user_1_id = 1 AND r.type = 'Customer')
  LEFT JOIN books b ON (b.id = t.book_id AND b.active)
  LEFT JOIN users u1 ON (u1.id = t.user_1_id) # AND u1.active
  LEFT JOIN users u2 ON (u2.id = t.user_2_id) # AND u2.active

  WHERE (r.user_2_id = t.user_1_id OR t.user_2_id = 1 AND t.user_1_id != 1)
    # AND b.active AND u1.active AND u2.active

[结果]：

| id | type |  timestamp | user_1_name | user_2_name |      book_name |
|----|------|------------|-------------|-------------|----------------|
|  3 | Rent | 1465238593 |       Alice |        Jess | Modern History |
|  2 | Rent | 1465238592 |       Alice |        John |         (null) | <<< Should not be here
|  7 | Rent | 1465238597 |       Alice |       Alice |       Red Mars |
|  5 | Sold | 1465238595 |       Alice |        Jess |         (null) | <<< Correct
|  9 | Rent | 1465238598 |       Alice |         Bob |         (null) | <<< Should not be here

在上面的结果中，问题是book neuromancer的flag'active'设置为0，因此不应该出现在结果中。我已经玩过了 AND b.active 在不同的地方，但结果总是错误的(看到了吗http://www.sqlfiddle.com/#!9/696（第2/5版）
看着上面的乱七八糟，我甚至不确定我的方法是否好，欢迎任何建议。