Re: How to best migrate this nested join ?

Поиск
Список
Период
Сортировка
От David G. Johnston
Тема Re: How to best migrate this nested join ?
Дата
Msg-id CAKFQuwbxMDhdGiBYe_cERMEjQ1wUsP9Gj0cYKV5ZLUfLNV2fsw@mail.gmail.com
обсуждение исходный текст
Ответ на How to best migrate this nested join ?  (Gary M <garym@oedata.com>)
Ответы Re: How to best migrate this nested join ?
Список pgsql-sql
Дерево обсуждения
On Thu, Mar 1, 2018 at 2:16 PM, Gary M <garym@oedata.com> wrote:
Hi,

I have this nested join taken from some old  sybase code.

SELECT

     TBL1.ID AS ID1,
     TBL4.ID AS ID2,
     TBL5.ID AS ID3,
     TBL6.ID AS ID4
FROM    TBL1, TBL2, TBL3, TBL4, TBL5, TBL6, TBL7

WHERE       TBL1.AA *= TBL2.AA    and TBL1.AA = TBL3.AA   and TBL4.AB = TBL3.CD   and AB = 'TBL4'  /* not sure where AB is coming from */   and TBL4.AB = TBL7.AB   and TBL7.ZZ = 0   and TBL4.AB *= TBL6.AB   and TBL4.AB *= TBL5.AB
;
How should these joins be nested using pg-sql ? 


​*= means left join, I presume​

​(not tested)​

FROM TBL1
JOIN TBL3 ON (TBL1.AA = TBL3.AA)
JOIN TBL4 ON (TBL3.CD = TBL4.AB)
JOIN TBL7 ON (TBL4.AB = TBL7.AB)
LEFT JOIN TBL2 ON (TBL1.AA = TBL2.AA)
LEFT JOIN TBL5 ON (TBL4.AB = TBL5.AB)
LEFT JOIN TBL6 ON (TBL4.AB = TBL6.AB)
​WHERE TBL4.AB = 'TBL4'
AND TBL7.ZZ = 0

I suggest the following related reading.

https://www.postgresql.org/docs/10/static/tutorial-join.html

David J.

В списке pgsql-sql по дате отправления: