Can't seem to mix an inner and outer join in a query and get it to work right.

On Monday, June 29, 2020, David Gauthier <davegauthierpg@gmail.com> wrote:

sqf_id | sqf_sl | as_cl |
wa_id | type

----------------------------------------------------+-------
--+---------+-------+-----------

* arazhang_20.06.28-20:59:06_soc_navi24_main@4404799 | 4404957 | 4405363
| 2772 | autosmoke*

arazhang_20.06.28-20:59:06_soc_navi24_main@4404799 | 4404957 | 4405363
| 2773 |

arazhang_20.06.28-20:59:06_soc_navi24_main@4404799 | 4404957 | 4405363
| 2774 |

* bgregory_20.06.29-09:46:49_raphael_main@1277530 | 1277949 |
| |*
(4 rows)

dvm.workarea_env on
dvdb-# (sqf.sqf_runs.submitted_
changelist=dvm.workarea_env.p4_changelist)
dvdb-# *inner join* dvm.dvm_events on
dvdb-# (dvm.workarea_env.wa_id = dvm.dvm_events.wa_id
and dvm.dvm_events.type = 'autosmoke')
dvdb-# where sqf.sqf_Runs.submitted_shelvelist in (4404957,1277949);
sqf_id | sqf_sl | as_cl |
wa_id | type
----------------------------------------------------+-------
--+---------+-------+-----------
arazhang_20.06.28-20:59:06_soc_navi24_main@4404799 | 4404957 | 4405363 |
2772 | autosmoke
(1 row)

Is there a way to retain the effect of that first outer join while
enforcing that inner join ?

The quick non-testable answer is most likely. I usually end up doing
trial-and-error and adding possibly unnecessary parentheses to force the
needed order of operations (or moving part of the join into an explicit
subquery, possibly using a CTE/WITH). Right now it seems that the query is
forcing, via the inner join, a final output where the column
type=‘autosmoke’. i.e., its doing the outer join first then the inner.
You seem to want the reverse.

David J.

Thanks!

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)

On Mon, Jun 29, 2020 at 9:10 PM David G. Johnston <
david.g.johnston@gmail.com> wrote:

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.