Reputation: 227
i have another case , i'm looking a query that can show null
value too,
first at all, i make table1;
+---------+----------+
|id_table1| value |
+---------+----------+
| 1 | value1 |
| 2 | value2 |
| 3 | value3 |
| 4 | value4 |
| 5 | value5 |
| 6 | value6 |
| 7 | value7 |
| 8 | value8 |
| 9 | value9 |
+---------+----------+
table2:
+---------+----------+
|id_table1| value |
+---------+----------+
| P1 | valueP1 |
| P2 | valueP2 |
+---------+----------+
and this is my relationship table:
+---------+----------+---------+
|id_boss | id_child | answ |
+---------+----------+---------+
| 1 | 2 | T |
| 1 | 6 | F |
| 2 | P1 | T |
| 2 | 4 | F |
| 6 | P2 | T |
| 6 | 8 | F |
+---------+----------+ --------+
i following this awesome answer to make my first query work, cek this SQLFiddle
+---------+-----------+-----------+
|id_boss | child_T | child_F |
+---------+-----------+-----------+
| value1 | value2 | value6 |
| value2 | valueP1 | value4 |
| value6 | valueP2 | value8 |
+---------+-----------+-----------+
and then here's my new problem,
from relationship table, let say, i'm removing some id_child value, so i can fill it later,
+---------+----------+---------+
|id_boss | id_child | answ |
+---------+----------+---------+
| 1 | 2 | T |
| 1 | 6 | F |
| 2 | P1 | T |
| 2 | 4 | F |
| 6 | | T |
| 6 | | F |
| 4 | | T |
| 4 | | F |
+---------+----------+ --------+
and then i'm looking a way to make my query look like this :
+---------+-----------+-----------+
|id_boss | child_T | child_F |
+---------+-----------+-----------+
| value1 | value2 | value6 |
| value2 | valueP1 | value4 |
| value6 | `null` | `null` |
| value4 | `null` | `null` |
+---------+-----------+-----------+
Upvotes: 0
Views: 83
Reputation: 36
Try this:
SELECT (SELECT value FROM table1 WHERE table1.id_table1 = r1.id_boss) AS id_boss,
COALESCE( (SELECT value FROM table1 WHERE table1.id_table1 = r1.id_child),
(SELECT value FROM table2 WHERE table2.id_table2 = r1.id_child)
) AS child_T,
COALESCE( (SELECT value FROM table1 WHERE table1.id_table1 = r2.id_child),
(SELECT value FROM table2 WHERE table2.id_table2 = r2.id_child)
) AS child_F
FROM r_table AS r1 , r_table AS r2
WHERE r1.id_boss = r2.id_boss AND r1.answ = 'T' AND r2.answ = 'F';
It works for me but if you have a problem let me know.
Upvotes: 2