如何联接多个记录和多个字段上的表

Question

我需要为相当复杂的mySQL连接提供解决方案： 假设我们有 3 个表：T1、T2 和 T3。 表 1 包含主 ID 和名称字段。

表1

| ID | Name |
|  1 |   A  |
|  2 |   B  |
|  3 |   C  |
|  4 |   D  |

表 2 包含表 1 的 ID 列和一个值

表2

| ID |  oID | Value |
|  1 |   1  | aaa   |
|  2 |   1  | bbb   |
|  3 |   1  | ccc   |
|  4 |   2  | ddd   |
|  5 |   2  | eee   |
|  6 |   3  | fff   |
|  7 |   3  | ggg   |
|  8 |   3  | hhh   |
|  9 |   4  | iii   |

到目前为止一切顺利，一个简单的 JOIN 将获得链接到 Table1.Name 的 Table2.Values 但也有表3

表3

| ID |  oID | Condition  | Value |
| 1  |   1  | color      | red   |
| 2  |   1  | brightness | 20    |
| 3  |   2  | color      | green |
| 4  |   2  | brightness | 50    |
| 5  |   2  | saturation | 100   |
| 6  |   3  | color      | green |
| 7  |   3  | brightness | 40    |
| 8  |   3  | saturation | 70    |
| 9  |   4  | color      | purple|

我需要做的是获取 T1.Name，以及所有相关的 T2。仅当 T3 的 2 个(或更多(条件正常时，值： 例如： 具有匹配 T2 的所有名称。值 IF T3。条件 = "绿色" 和 T3.亮度 = 50 由于只有ID 2是绿色的，亮度为50，这应该给我

B ddd
B eee

我尝试了几件事：

SELECT  t1.ID,
t1.Name,
t2.Value
FROM
Table1 t1
JOIN Table2 t2  ON t1.ID = t2.oID
JOIN Table3 t3 ON t1.ID = t3.oID
WHERE 
t3.Condition = 'color'
AND
t3.Value = 'green'
AND
t3.Condition = 'brightness'
AND
t3.Value = 50
GROUP BY p.ID

当然什么也没结果，因为没有记录具有所有四个条件

SELECT  t1.ID,
t1.Name,
t2.Value
FROM
Table1 t1
JOIN Table2 t2  ON t1.ID = t2.oID
JOIN Table3 t3 ON t1.ID = t3.oID
WHERE 
(t3.Condition = 'color' AND t3.Value = 'green')
AND
(t3.Condition = 'brightness'AND t3.Value = 50)
GROUP BY p.ID

相同的结果：没有

SELECT  t1.ID,
t1.Name,
t2.Value
FROM
Table1 t1
JOIN Table2 t2  ON t1.ID = t2.oID
JOIN Table3 t3 ON t1.ID = t3.oID
WHERE 
(t3.Condition = 'color' AND t3.Value = 'green')
OR
(t3.Condition = 'brightness' AND    t3.Value = 50)
GROUP BY p.ID

我得到所有亮度为 50 的记录和绿色的记录......但不是那些同时具备这两种情况的人。

我还尝试了 JOIN ON 语句中的条件，左连接，其中条件在 (..,..( 等，但没有成功。

找到解决方案的人将获得永恒的名声！

Answer 1

SELECT * FROM table3;
+----+-----+------------+--------+
| ID | oID | condition  | value  |
+----+-----+------------+--------+
|  1 |   1 | color      | red    |
|  2 |   1 | brightness | 20     |
|  3 |   2 | color      | green  |
|  4 |   2 | brightness | 50     |
|  5 |   2 | saturation | 100    |
|  6 |   3 | color      | green  |
|  7 |   3 | brightness | 40     |
|  8 |   3 | saturation | 70     |
|  9 |   4 | color      | purple |
+----+-----+------------+--------+
9 rows in set (0.00 sec)
mysql>
SELECT x.oid
FROM table3 x
WHERE (x.condition,x.value) IN(('color','green'),('brightness','50'))
GROUP
BY x.oid
HAVING COUNT(*) = 2;
+-----+
| oid |
+-----+
|   2 |
+-----+

请注意，condition是一个保留字，使其成为表/列标识符不太理想的选择

Answer 2

你做错了什么，你把table3连接到其他两个表。
相反，您应该联接一个table3的子查询，该子查询返回满足条件的oID：

SELECT t1.name, t2.value
FROM table1 t1
INNER JOIN table2 t2 ON t1.id = t2.oid
INNER JOIN (
SELECT oID FROM table3
GROUP BY oID
HAVING SUM(`condition` = 'color' AND value = 'green') > 0
AND SUM(`condition` = 'brightness' AND value = '50') > 0
) t3 ON t1.id = t3.oID

请参阅演示。
结果：

| name | value |
| ---- | ----- |
| B    | ddd   |
| B    | eee   |