The below query gives me M, A or B depending WHERE the COLA is present.
If present in TBLM --> gives M
present in TBLA --> gives A
present in TBLB --> gives B


SELECT CASE
WHEN A.COLA IS NOT NULL
AND B.COLA IS NOT NULL THEN 'M'
WHEN A.COLA IS NOT NULL THEN 'A'
WHEN B.COLA IS NOT NULL THEN 'B'
END
FROM TBLM
LEFT OUTER JOIN TBLA
ON A.COLA = M.COLA
LEFT OUTER JOIN TBLB
ON B.COLA = M.COLA

WHERE M.COLA = XXX

I need to convert the SQL to give me 'Y' when COLA is present in
1) TBLM and TBLA
OR
2) TBLM and TBLB
OR
3) TLM and TBLA and TBLB
Please advise..

The phrase "is present" is not so clear for me.
Is that "NOT NULL"?
Or, a row was retrieved?

In either case, I couldn't understand the relationship of the followings.
and
Because, rows of TBLM are always retrieved if the predicate "M.COLA = XXX" was true,
regardless the rows of TBLA or TBLB were retrieved or not.

Anyhow, answer to your question might be:
WHEN A.COLA IS NOT NULL OR B.COLA IS NOT NULL THEN 'Y'

Yes..I meant 'is present meaning NOT NULL'.

TBLM is NOT NULL
and also
either (TBLA or TBLB) is NOT NULL.

Sorry for confusion.

'Y' when
1) TBLM.COLA IS NOT NULL and TBLA.COLA IS NOT NULL
OR
2) TBLM.COLA IS NOT NULL and TBLB.COLA IS NOT NULL
OR
3) TBLM.COLA IS NOT NULL and TBLA.COLA IS NOT NULL and TBLB.COLA IS NOT NULL

WHEN TBLA.COLA IS NOT NULL OR TBLB.COLA IS NOT NULL THEN 'Y'

Because, if TBLM.COLA IS NULL,
no row of TBLA nor TBLB will be joined then TBLA.COLA and TBLB.COLA are always NULL.

Yes..that works..many thanks.