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 | | |
(4 rows)
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 ?
