dionajie dionajie - 2 months ago 5
SQL Question

SQL Server : Select multiple table and show relation even NULL

i Have 3 tables.

Issue Table

___________________
id_issue | issue |
1 | issue A |
2 | issue B |
3 | issue C |
___________________


Problem Table

_______________________
id_problem | problem |
1 | problem A |
2 | problem B |
3 | problem C |
_______________________


Visi Table. Have fk on 2 tables before

__________________________________________
id_visi | visi | id_problem | id_issue |
1 | visi A | 1 | 1 |
2 | visi B | 1 | 2 |
3 | visi C | 1 | 3 |
4 | visi D | 2 | 1 |
5 | visi E | 2 | 2 |
___________________________________________


i want to combine 3 tables like this. :
[EDIT]

_____________________________________
| visi | problem | issue |
| visi A | problem A | issue A |
| visi B | problem A | issue B |
| visi C | problem A | issue C |
| visi D | problem B | issue A |
| visi E | problem B | issue B |
| NULL | problem B | issue C |
| NULL | problem C | issue A |
| NULL | problem C | issue B |
| NULL | problem C | issue C |
_____________________________________


I had tried JOIN table but it didnt work. The result didn't show NULL's row.

Answer

You need combination of CROSS JOIN & LEFT JOIN

SELECT visi, 
       ip.id_problem, 
       ip.id_issue 
FROM   (SELECT * 
        FROM   issue I 
               CROSS JOIN problem P) ip 
       LEFT JOIN visi v 
              ON ip.id_problem = v.id_problem 
                 AND ip.id_issue = v.id_issue 

CROSS JOIN : To populate all possible combination between two tables (Issue & Problem).

LEFT JOIN : To pull data for matched records from Visi and NULL for not matched records

Comments