Postgres Pro
Downloads
Home   >   mailing lists

Re: How to best migrate this nested join ? - Mailing list pgsql-sql

From David G. Johnston
Subject Re: How to best migrate this nested join ?
Date
Msg-id CAKFQuwbxMDhdGiBYe_cERMEjQ1wUsP9Gj0cYKV5ZLUfLNV2fsw@mail.gmail.com
Whole thread Raw
In response to How to best migrate this nested join ?  (Gary M <garym@oedata.com>)
Responses Re: How to best migrate this nested join ?  (Gary M <garym@oedata.com>)
List pgsql-sql
Tree view
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 by date:

Previous
From: Gary M
Date:
Subject: How to best migrate this nested join ?
Next
From: Gary M
Date:
Subject: Re: How to best migrate this nested join ?