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 ?
