9.6.7 on linux This query, which has 2 outer joins, gives me the records that I want... dvdb=# select dvdb-# sqf.sqf_runs.sqf_id, dvdb-# sqf.sqf_runs.submitted_shelvelist as sqf_sl, dvdb-# dvm.workarea_env.p4_changelist as as_cl, dvdb-# dvm.workarea_env.wa_id, dvdb-# dvm.dvm_events.type dvdb-# from dvdb-# sqf.sqf_runs left outer join dvm.workarea_env on dvdb-# (sqf.sqf_runs.submitted_changelist=dvm.workarea_env.p4_changelist) dvdb-# left outer 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 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 | |
| The last 3 rows have nothing in the last column because of the first outer join in the query. The last row has nothing in the 3rd and 4th columns because of the 2nd outer join. I want to change the query to keep the first and last rows. So I was thinking to change the second outer join to an inner join. But when I run it, I lose the 4th record... dvdb=# select dvdb-# sqf.sqf_runs.sqf_id, dvdb-# sqf.sqf_runs.submitted_shelvelist as sqf_sl, dvdb-# dvm.workarea_env.p4_changelist as as_cl, dvdb-# dvm.workarea_env.wa_id, dvdb-# dvm.dvm_events.type dvdb-# from dvdb-# sqf.sqf_runs left outer join 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 ? |
On Monday, June 29, 2020, David Gauthier <[hidden email]> wrote:
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 <[hidden email]> wrote: On Monday, June 29, 2020, David Gauthier <[hidden email]> wrote: |
The convention here is to bottom post or inline responses. On Wed, Jul 1, 2020 at 9:51 AM David Gauthier <[hidden email]> wrote:
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. |
Free forum by Nabble | Edit this page |