PostgreSQL › PostgreSQL - general

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

_‹ Previous Topic Next Topic _›

Classic

List

Threaded

4 messages Options

David Gauthier

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

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 ?

David G Johnston

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

On Monday, June 29, 2020, David Gauthier <[hidden email]> 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.

David Gauthier

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

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:

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.

David G Johnston

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

The convention here is to bottom post or inline responses.

On Wed, Jul 1, 2020 at 9:51 AM David Gauthier <[hidden email]> wrote:

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)

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.