PostgreSQL Same Table Left Outer Join With Multiple Tables

Question

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"

foibs · Accepted Answer · 2013-12-31 14:43:44Z

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"

Collectives™ on Stack Overflow

PostgreSQL Same Table Left Outer Join With Multiple Tables

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related