Trouble in querying data I'm having difficulty in retrieving data from tables.
Bellow is the scenario I'm stuck at.
TableA




Id
Name




1
ABC


2
DEF


3
GHI


4
JKL




TableB




Id
TableA_Id
Tag




1
1
X


2
2
Y


3
0
Z




TableC




Id
TableA_Id
Something




1
1
XXX


2
1
XXX


3
1
XXX


4
2
XXX


5
2
XXX


6
3
XXX


7
3
XXX


8
4
XXX


9
4
XXX




TableA joins With TableB by TableA_Id column, the only difference is that where there is no reference of TableA id then we are supposed to return the row with TableA_Id = 0 from TableB.
Required Output




Name
Tag
Something




ABC
X
XXX


ABC
X
XXX


ABC
X
XXX


DEF
Y
XXX


DEF
Y
XXX


GHI
Z
XXX


GHI
Z
XXX


JKL
Z
XXX


JKL
Z
XXX
SELECT t1.name,
       COALESCE(t2.tag, t3.tag) tag,
       t4.something
FROM tableA t1
LEFT JOIN tableB t2 ON t1.id = t2.TableA_Id
JOIN tableB t3 ON t3.TableA_Id = 0
JOIN tableC t4 ON t1.id = t4.TableA_Id

TableB must contain only one row with TableB.TableA_Id = 0. If not then use
SELECT t1.name,
       COALESCE(t2.tag, t3.tag) tag,
       t4.something
FROM tableA t1
LEFT JOIN tableB t2 ON t1.id = t2.TableA_Id
CROSS JOIN ( SELECT *
             FROM tableB 
             WHERE TableA_Id = 0
             -- ORDER BY something, for example, by `Id`
             LIMIT 1 ) t3
JOIN tableC t4 ON t1.id = t4.TableA_Id

fiddle