Postgres Pro
Downloads
Home   >   mailing lists

Re: Can't seem to mix an inner and outer join in a query and get it to work right. - Mailing list pgsql-general

From David G. Johnston
Subject Re: Can't seem to mix an inner and outer join in a query and get it to work right.
Date
Msg-id CAKFQuwbmk4R9yBYy+EE9+=5PeNx3rWrGsDfqEFLOEBe3wLGMpA@mail.gmail.com
Whole thread Raw
In response to Re: Can't seem to mix an inner and outer join in a query and get it to work right.  (David Gauthier <davegauthierpg@gmail.com>)
List pgsql-general
Tree view
The convention here is to bottom post or inline responses.

On Wed, Jul 1, 2020 at 9:51 AM David Gauthier <davegauthierpg@gmail.com> wrote:
Actually, I want the outer join first.  If it finds something, then move on to the inner join and filter out all those that don't join to a rec with 'autosmoke'.  But if the outer join does not connect to the workarea_env table, then just return what you have (the purpose of the outer join)


So your final result - ignoring columns - is basically:
(sqf, (workarea, events))

where either the entire (workarea, events) is null, or if it is non-null then workarea must also be non-null

Thus: ((workarea is left joined against events) with the whole thing left joined against sqf).  And we are back to the join ordering precedence since what you originally wrote was ((sqf, workarea), events).

In short - two outer joins; you can work out precedence either with syntactic order or parentheses.

David J.

pgsql-general by date:

Previous
From: FOUTE K. Jaurès
Date:
Subject: Re: PostgreSQL 12 - ERROR: could not rename file "pg_logical/snapshots/1A-7C00D890.snap.13594.tmp" to "pg_logical/snapshots/1A-7C00D890.snap": No space left on device
Next
From: FOUTE K. Jaurès
Date:
Subject: Re: PostgreSQL 12 - ERROR: could not rename file "pg_logical/snapshots/1A-7C00D890.snap.13594.tmp" to "pg_logical/snapshots/1A-7C00D890.snap": No space left on device