Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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 ?



  
 

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux