I have 2 tables in Postgresql. Both these are in different schema. I want the output as in the shared screenshot.
Suppose,for field HDRConfig,if the output value is SE then I want the output as Software Engineering instead of SE. How do I show these with the rest of the columns i.e HDRConfig, AbrConfig & SbrConfig ?
Simple LEFT JOIN:
SELECT l0.Code_Desc AS HDRConfig,
l1.Code_Desc AS AbrConfig,
l2.Code_Desc AS SbrConfig
FROM Equipment e
LEFT JOIN Lookup l0
ON e.HDRConfig = l0."Code"
LEFT JOIN Lookup l1
ON e.AbrConfig = l1."Code"
LEFT JOIN Lookup l2
ON e.SbrConfig = l2."Code";
╔═══════════════════════╦═══════════════════════╦══════════════════════╗
║ hdrconfig ║ abrconfig ║ sbrconfig ║
╠═══════════════════════╬═══════════════════════╬══════════════════════╣
║ Software Engineering ║ Software Engineering ║ Software Engineering ║
║ (null) ║ Analog System ║ Floppy Primary ║
╚═══════════════════════╩═══════════════════════╩══════════════════════╝
Using correletad subqueries(inefficient way):
SELECT
(SELECT Code_desc FROM Lookup WHERE "Code" = e.HDRConfig) AS HDRConfig,
(SELECT Code_desc FROM Lookup WHERE "Code" = e.ABRConfig) AS ABRConfig,
(SELECT Code_desc FROM Lookup WHERE "Code" = e.SbrConfig) AS SbrConfig
FROM Equipment e;
