CODEWITHSUNDEEP
postgresqlleft-join
Calipso
Calipso

Reputation: 967

PostgreSQL Same Table Left Outer Join With Multiple Tables

I have a table which needs to left outer joined with two different tables. When I put the table twice in the query and join it with it self in the where clause (like the sentence below) it works. I think this should not be the correct way. How can I write the select statement and outer join the table with multiple different tables?

SELECT cols."COLUMN_NAME"
     , COALESCE(translations."COLUMN_LANG_TITLE",cols."COLUMN_TITLE") AS "COLUMN_TITLE" 
     , options."OPTION_DESC"
  FROM 

"EXTAPP_SETUP"."OBJECT_COLUMNS_TAB" cols LEFT OUTER JOIN "EXTAPP_SETUP"."COLUMN_LANG_TITLES_TAB" translations
        ON translations."EXTAPP_ID" = cols."EXTAPP_ID" and translations."OBJECT_NAME" = cols."OBJECT_NAME" and translations."COLUMN_NAME" = cols."COLUMN_NAME" and translations."LANGUAGE_CODE" = 'fr',

"EXTAPP_SETUP"."OBJECT_COLUMNS_TAB" cols2 LEFT OUTER JOIN "EXTAPP_SETUP"."COL_FIX_OPTIONS_TAB" options
        ON options."EXTAPP_ID" = cols2."EXTAPP_ID" and options."OBJECT_NAME" = cols2."OBJECT_NAME" and options."COLUMN_NAME" = cols2."COLUMN_NAME" 

WHERE cols."EXTAPP_ID" = cols2."EXTAPP_ID" and cols."OBJECT_NAME" = cols2."OBJECT_NAME" and cols."COLUMN_NAME" = cols2."COLUMN_NAME"

Upvotes: 2

Views: 16510

Answers (1)

foibs
foibs

Reputation: 3406

You can use subsequent left joins. Since you are not realy using your cols2 table, you can eliminate it from the query. This would also eliminate the where clause, because you don't need to join the cols table with itself

SELECT cols."COLUMN_NAME"
  , COALESCE(translations."COLUMN_LANG_TITLE",cols."COLUMN_TITLE") AS "COLUMN_TITLE" 
  , options."OPTION_DESC"
FROM "EXTAPP_SETUP"."OBJECT_COLUMNS_TAB" cols 
LEFT OUTER JOIN "EXTAPP_SETUP"."COLUMN_LANG_TITLES_TAB" translations
  ON translations."EXTAPP_ID" = cols."EXTAPP_ID" 
    and translations."OBJECT_NAME" = cols."OBJECT_NAME" 
    and translations."COLUMN_NAME" = cols."COLUMN_NAME" 
    and translations."LANGUAGE_CODE" = 'fr' 
LEFT OUTER JOIN "EXTAPP_SETUP"."COL_FIX_OPTIONS_TAB" options
  ON options."EXTAPP_ID" = cols."EXTAPP_ID" 
    and options."OBJECT_NAME" = cols."OBJECT_NAME" 
    and options."COLUMN_NAME" = cols."COLUMN_NAME"

Upvotes: 6

Related Questions