Как объединить таблицы с несколькими записями и несколькими полями

Мне нужно решение для довольно сложного соединения mySQL: скажем, у нас есть 3 таблицы: T1, T2 и T3. Таблица 1 содержит основной идентификатор и поле имени.

Таблица 1

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

Таблица 2 содержит столбец для идентификатора таблицы 1 и значение

Таблица 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   |

Пока все хорошо, простое СОЕДИНЕНИЕ даст мне Table2.Values, связанное с Table1.Name. Но есть также Таблица 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.Values ​​ТОЛЬКО ЕСЛИ 2 (или более) условий T3 в порядке: например: все имена с соответствующими T2.Values ​​IF T3.Condition = 'green' AND T3.brightness = 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, LEFT JOINS, WHERE condition IN (..,..) и т. д., но безуспешно.

Тот, кто находит решение, получает вечную славу!


mysql join multiple-records
person vasch    schedule 22.09.2019    source источник
comment
Не могли бы вы сказать нам, какую версию MySQL вы используете?   -  person Schwern    schedule 22.09.2019

Ответы (2)


arrow_upward
0
arrow_downward

Что вы делаете неправильно, так это то, что вы присоединяете table3 к другим 2 таблицам.
Вместо этого вы должны присоединиться к подзапросу 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   |
person forpas    schedule 22.09.2019
comment
Ага! полностью понять это сейчас, и это работает просто отлично. Вечная слава это! - person vasch; 23.09.2019

arrow_upward
1
arrow_downward 

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 является зарезервированным словом, что делает его далеко не идеальным выбором в качестве идентификатора таблицы/столбца.

person Strawberry    schedule 22.09.2019
comment
Это очень хорошо. Недостающий бит должен присоединиться к имени и значениям. dbfiddle - person Schwern; 22.09.2019