Im trying to get a value from outer join table but didnt work.. Need ur help..
1)Assume Tbl_report as below
RID | CAT_ID | EXT_CODE
----------------------------------
1 | C01 | HW06
2 | C02 | SW04
3 | C03 | IT04
4 | C04 | HW04
5 | C05 | SW02
WHERE EXT_CODE @ Tbl_report above is referring from more than 1 table (column SCODE from Tbl_SW, column HCODE from Tbl_HW or column ICODE from Tbl_IT) then, display the content of referrence table (which is content from column SNAME from Tbl_SW, HNAME from Tbl_HW or INAME from Tbl_IT)
2)Tbl_SW
SID | SCODE | SNAME
-----------------------------------
1 | SW02 | SEC 2
2 | SW04 | SEC 4
3 | SW06 | SEC 6
3)Tbl_HW
HID | HCODE | HNAME
-----------------------------------
1 | HW02 | HOC 2
2 | HW04 | HOC 4
3 | HW06 | HOC 6
4)Tbl_IT
IID | ICODE | INAME
-----------------------------------
1 | IT02 | ITC 2
2 | IT04 | ITC 4
3 | IT06 | ITC 6
Expected result as below :
RID | CAT_ID | EXT_CODE
----------------------------------
1 | C01 | HW06 - [HOC 6]
2 | C02 | SW04 - [SEC 4]
3 | C03 | IT04 - [ITC 4]
4 | C04 | HW04 - [HOC 4]
5 | C05 | SW02 - [SEC 2]
With this select statement
SELECT
`[RID]`,
`[CAT_ID]`,
CONCAT(`[EXT_CODE]`, ' - ',
`[SNAME]` ) result
FROM
Tbl_report tr
LEFT join (SELECT * FROM Tbl_SW UNION SELECT * FROM Tbl_HW UNION SELECT * FROM Tbl_IT) ts
ON tr.`[EXT_CODE]` = ts.`[SCODE]`
You get this result
RID CAT_ID result
1 C01 HW06 - HOC 6
2 C02 SW04 - SEC 4
3 C03 IT04 - ITC 4
4 C04 HW04 - HOC 4
5 C05 SW02 - SEC 2
DBfiddle example https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=f78a88f019df887666ac059d39ba1f48
This work because your tables to join have teh same structure.
Left join i put there because in your first example you had it03 in it