Thread: How to best migrate this nested join ?

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 ID4FROM    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.