MySQL连接不同结构的表

Question

我在以下问题中无法获得任何结果或正确结果 - http://www.sqlfiddle.com/#!9/696ed2/4

总体目标是列出作为“客户”链接在一起的用户的所有交易。因此，如果约翰正在查看他的仪表板，他将看到爱丽丝(他的客户)租了哪些书(包括书名)，以及售出了哪些书(他将看不到那本书的书名)。

当两个多表连接到父表时，两个依赖表的每一行都设置了“事件”标志，我似乎无法只获得事件行。

# 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

在上面的结果中，问题在于《Neuromancer》一书的标志“active”设置为 0，因此不应出现在结果中。我试过将 AND b.active 放在不同的地方，但结果总是错误的。 (参见 http://www.sqlfiddle.com/#!9/696ed2/5)

看到上面的乱七八糟的东西，我什至不确定我的方法有什么好处，欢迎任何建议。

Answer 1

正如 D. Smania 在评论中提到的，您需要确保 b.active 是 1 或 NULL 但基于您的 LEFT JOIN 条件 b.active 将始终为 1 因此您只需要对 id 进行连接并依赖于WHERE 比较条件。这应该会产生您要求的结果:

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)
    LEFT JOIN users u1 ON (u1.id = t.user_1_id)
    LEFT JOIN users u2 ON (u2.id = t.user_2_id)
WHERE (r.user_2_id = t.user_1_id OR t.user_2_id = 1 AND t.user_1_id != 1)
    AND (b.active OR b.active IS NULL)
    AND u1.active AND u2.active

SQL Fiddle

请注意 - 在您的第一个 WHERE 条件中，我不清楚您的意思是:

(r.user_2_id = t.user_1_id OR (t.user_2_id = 1 AND t.user_1_id != 1))

或

((r.user_2_id = t.user_1_id OR t.user_2_id = 1) AND t.user_1_id != 1)

当您有相邻的 AND 和 OR 条件时，最好明确您的逻辑分组。