MySQL Join 4 Tables

[Thames Muthu]

I have 4 tables table A,B,C and D and structures are given below.

Table A Table B Table C Table D
ID Des ID P_ID P_ID P_DATE ID REF_NO D_DATE
1 xxx 1 P01 P01 2022/10/12 2 B13 2022/09/30 2 yyy 3 P02 P02 2022/10/19 4 L84 2022/09/22
3 zzz
4 aaa

and so on. pk for Table A is ID, pk for Table C is P_ID. I need to produce the following result using a query for a given criteria (ID). Query should produce either there are matching records in table C or D as follows.


A.ID A.Des C.P_DATE C.ITEM_NO D.REF_NO D.D_DATE
1 xxx 2022/10/12 40 ________ ________
1 xxx 2022/11/05 03 ________ ________
3 zzz 2022/10/19 24 ________ ________
2 yyy _______ ________ B13 2022/09/30
4 aaa _______ ________ L84 2022/09/22

Note: I wrote a union query that produces the same result. But I prefer to get a single result set without a UNION which is easy to further process. Your advice is highly appreciated.