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

From: David Gauthier <davegauthierpg(at)gmail(dot)com>
To: Postgres General <pgsql-general(at)postgresql(dot)org>
Subject: Can't seem to mix an inner and outer join in a query and get it to work right.
Date: 2020-06-30 00:27:07
Message-ID: CAMBRECB+_=EtxQwKBMzQ2N9_8jp3QZ2e5ffOV24WRWmEPXypmw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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(at)4404799 | 4404957 | 4405363 |
2772 | autosmoke*

arazhang_20.06.28-20:59:06_soc_navi24_main(at)4404799 | 4404957 | 4405363 |
2773 |

arazhang_20.06.28-20:59:06_soc_navi24_main(at)4404799 | 4404957 | 4405363 |
2774 |

* bgregory_20.06.29-09:46:49_raphael_main(at)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(at)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 ?

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Samuel Williams 2020-06-30 00:50:22 Re: libpq pipelineing
Previous Message Tom Lane 2020-06-29 22:59:54 Re: PostgreSQL database segsize