From: | Gary M <garym(at)oedata(dot)com> |
---|---|
To: | |
Cc: | pgsql-sql(at)lists(dot)postgresql(dot)org |
Subject: | Re: How to best migrate this nested join ? |
Date: | 2018-03-02 00:15:09 |
Message-ID: | CAGwOJny85+o9KAn7X4NySbw=Vxp9jPtyMeucaTGEKLtTACg=ug@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
Thanks David,
I'm just coming up to speed on pg after 10-15 years away.
On Thu, Mar 1, 2018 at 2:31 PM, David G. Johnston <
david(dot)g(dot)johnston(at)gmail(dot)com> wrote:
> 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.
>
>
From | Date | Subject | |
---|---|---|---|
Next Message | Stanton Schmidt | 2018-03-08 18:58:25 | Query question |
Previous Message | David G. Johnston | 2018-03-01 21:31:19 | Re: How to best migrate this nested join ? |