How to join tables on multiple records and multiple fields

Question

I need a solution for a rather complex mySQL join: Say we have 3 tables: T1, T2 and T3. Table 1 contains the main ID and a name field.

Table1

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

Table 2 contains a column for the Table 1's ID and a value

Table2

| 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   |

So far so good, a simple JOIN will get me the Table2.Values linked to the Table1.Name But there is also Table 3

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|

What I need to do is get the T1.Name, with all the related T2.Values ONLY IF 2 (or more) conditions of T3 are OK: For example: All Names with matching T2.Values IF T3.Condition = 'green' AND T3.brightness = 50 As only ID 2 is green with a brightness of 50, that should give me

B ddd
B eee

I have tried several things:

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

Resulting in nothing of course, as no record has all four conditions

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

Same result: nothing

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

I get all records with brightness 50 and those with color green... but not those with both those conditions.

I also tried with conditions in the JOIN ON statement, LEFT JOINS, WHERE condition IN (..,..) and so on but without success.

The one who finds the solution gets eternal fame!

Answer 1

What you are doing wrong is that you join table3 to the other 2 tables.
Instead you should join a subquery of table3 which returns the oIDs that meet your conditions:

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

See the demo.
Results:

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

Answer 2

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 |
+-----+

Note that condition is a reserved word, making it a less than ideal choice as a table/column identifier