Re: How to best migrate this nested join ?

On Thu, Mar 1, 2018 at 2:16 PM, Gary M <garym(at)oedata(dot)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.