0

i want to join 3 table with null value,
here i have 3 table folowing

tbl_reporting                               tbl_criteria                   tbl_student
|===============================|           |========================|     |===============|
| id | id_criteria | id_student |           | id |   name    |weight |     | id |   name   |
|===============================|           |========================|     |===============|
| 1  |     2       |      3     |           |  1 | worrying  |  3    |     | 1  | Nina     |
| 2  |     1       |      2     |           |  2 | naughty   |  2    |     | 2  | Adam     |
| 3  |     1       |      1     |           |  3 | usually   |  2    |     | 3  | Dodi     |
| 4  |     2       |      2     |           |  4 | good      |  1    |     | 4  | Zarah    |
| 5  |     1       |      1     |           |  5 | obey      |  1    |     | 5  | Udep     |

and the result i want, like this


result
| id | student_name   | criteria_name | weight |
|==============================================|
| 1  | Dodi           |    naughty    |   2    |
| 2  | Adam           |    worrying   |   3    |
| 3  | Nina           |    worrying   |   3    |
| 4  | Adam           |    naughty    |   2    |
| 5  | Nina           |    worrying   |   3    |
|NULL| Zarah          |      NULL     |   0    |    
|NULL| Udep           |      NULL     |   0    |    
|NULL| NULL           |     usualy    |   0    |    
|NULL| NULL           |      good     |   0    |    
|NULL| NULL           |      obey     |   0    |

i try myself.. but not what i expected. here i code self : link
thanks

1
SELECT r.id, s.name student_name, c.name criteria_name, c.weight
FROM tbl_reporting r
JOIN tbl_student s ON r.id_student = s.id
JOIN tbl_criteria c ON r.id_criteria = c.id
UNION ALL
SELECT NULL, s.name, NULL, 0
FROM tbl_student s
WHERE NOT EXISTS ( SELECT NULL
                   FROM tbl_reporting r
                   WHERE r.id_student = s.id )

UNION ALL
SELECT NULL, NULL, c.name, 0
FROM tbl_criteria c
WHERE NOT EXISTS ( SELECT NULL
                   FROM tbl_reporting r
                   WHERE r.id_criteria = c.id );

https://dbfiddle.uk/?rdbms=mariadb_10.3&fiddle=989338bb30f2f5b4d7be4667763e9522

1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.