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

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

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

David Gauthier
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 ?



  
 
Reply | Threaded
Open this post in threaded view
|

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

David G Johnston
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.

Reply | Threaded
Open this post in threaded view
|

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

David Gauthier
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.

Reply | Threaded
Open this post in threaded view
|

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

David G Johnston
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.