PostgreSql Right Join From 3 Tables [closed]

I have 3 tables

Registrations

no_reg	name
20210520	A
20210521	B
20210522	C
20210523	D
20210524	E

ICD

no_icd	no_reg
A000	20210520
A001	20210521
A002	20210522
A003	20210523
A004	20210524

DTD

no_dtd	description	no_icd
DTD0	DTD A	A000
DTD1	DTD B	A000
DTD2	DTD C	A222
DTD3	DTD D	A333
DTD4	DTD E	A444

I want to join the three tables to obtain this result:

no_dtd	description	no_icd	no_reg	name
DTD0	DTD A	A000	20210520	A
DTD1	DTD B	A000	20210520	A
DTD2	DTD C	A222	NULL	NULL
DTD3	DTD D	A333	NULL	NULL
DTD4	DTD E	A444	NULL	NULL

This is my query:

SELECT
     dtd.no_dtd,
     dtd.description,
     icd.no_icd,
     registration.no_reg,
     registration.name
 FROM registration
 INNER JOIN icd ON registration.no_reg = icd.no_reg 
 RIGHT JOIN dtd ON icd.no_icd = dtd.no_icd 
 ORDER BY registration.name ASC;

But somehow the rows containing NULL are not being shown and I want them to appear.

My query result

no_dtd	description	no_icd	no_reg	name
DTD0	DTD A	A000	20210520	A
DTD1	DTD B	A000	20210520	A

Answer

I have created a little fiddle using this data

CREATE TABLE REGISTRATIONS (
    no_reg int,
    name VARCHAR (50), 
    PRIMARY KEY(no_reg)
);

CREATE TABLE ICD (
    no_icd VARCHAR(4),
    no_reg int NOT NULL,
    PRIMARY KEY(no_icd)
);

CREATE TABLE DTD (
    no_dtd VARCHAR(4),
    description VARCHAR ( 50 ),
    no_icd VARCHAR(4),
    PRIMARY KEY(no_dtd)
);


INSERT INTO REGISTRATIONS (no_reg, name) VALUES 
(20210520,  'A'),
(20210521,  'B'),
(20210522,  'C'),
(20210523,  'D'),
(20210524,  'E');

INSERT INTO ICD (no_icd, no_reg) VALUES 
('A000',    20210520),
('A001',    20210521),
('A002',    20210522),
('A003',    20210523),
('A004',    20210524);

INSERT INTO DTD (no_dtd, description, no_icd) VALUES
('DTD0',    'DTD A',    'A000'),
('DTD1',    'DTD B',    'A000'),
('DTD2',    'DTD C',    'A222'),
('DTD3',    'DTD D',    'A333'),
('DTD4',    'DTD E',    'A444');

It seems that you want ALL records in DTD table to be shown so this should be the “master” table of the join operations and it should be located at the left.

SELECT D.*, I.no_reg, R.name
FROM DTD D 
LEFT JOIN ICD I ON D.no_icd = I.no_icd
LEFT JOIN REGISTRATIONS R ON I.no_reg = R.no_reg;

Obtained result

no_dtd  description no_icd  no_reg      name
DTD0    DTD A       A000    20210520    A
DTD1    DTD B       A000    20210520    A
DTD2    DTD C       A222    (null)      (null)
DTD3    DTD D       A333    (null)      (null)
DTD4    DTD E       A444    (null)      (null)