On Monday, June 29, 2020, David Gauthier <davegauthie...@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.
