[prev in list] [next in list] [prev in thread] [next in thread]
List: postgresql-general
Subject: Re: Can't seem to mix an inner and outer join in a query and get it to work right.
From: "David G. Johnston" <david.g.johnston () gmail ! com>
Date: 2020-06-30 1:10:24
Message-ID: CAKFQuwayju34rpTYc+0-fnHSqjjAGCm3vV7VxcOyGP4ZKNnrAQ () mail ! gmail ! com
[Download RAW message or body]
On Monday, June 29, 2020, David Gauthier <davegauthierpg@gmail.com> 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.
[Attachment #3 (text/html)]
On Monday, June 29, 2020, David Gauthier <<a \
href="mailto:davegauthierpg@gmail.com">davegauthierpg@gmail.com</a>> \
wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px \
#ccc solid;padding-left:1ex"><div dir="ltr"><div class="gmail_default" \
style="font-family:monospace,monospace"><br></div><div class="gmail_default" \
style="font-family:monospace,monospace">
<p class="MsoNormal" style="margin:0in 0in \
0.0001pt;font-family:Calibri,sans-serif"><span style="font-family:"Courier \
New""> sqf_id \
| sqf_sl | as_cl | wa_id | type
</span></p>
<p class="MsoNormal" style="margin:0in 0in \
0.0001pt;font-family:Calibri,sans-serif"><span style="font-family:"Courier \
New"">------------------------------<wbr>----------------------+-------<wbr>--+---------+-------+---------<wbr>--</span></p>
<p class="MsoNormal" style="margin:0in 0in \
0.0001pt;font-family:Calibri,sans-serif"><span style="font-family:"Courier \
New""><b> arazhang_20.06.28-20:59:06_<wbr>soc_navi24_main@4404799 | 4404957 | \
4405363 | 2772 | autosmoke</b></span></p>
<p class="MsoNormal" style="margin:0in 0in \
0.0001pt;font-family:Calibri,sans-serif"><span style="font-family:"Courier \
New""> arazhang_20.06.28-20:59:06_<wbr>soc_navi24_main@4404799 | 4404957 | \
4405363 | 2773 | </span></p>
<p class="MsoNormal" style="margin:0in 0in \
0.0001pt;font-family:Calibri,sans-serif"><span style="font-family:"Courier \
New""> arazhang_20.06.28-20:59:06_<wbr>soc_navi24_main@4404799 | 4404957 | \
4405363 | 2774 | </span></p>
<p class="MsoNormal" style="margin:0in 0in \
0.0001pt;font-family:Calibri,sans-serif"><span style="font-family:"Courier \
New""><b> bgregory_20.06.29-09:46:49_<wbr>raphael_main@1277530 | 1277949 \
| | |</b><br></span><span \
style="font-family:"Courier New"">(4 rows)</span></p><p class="MsoNormal" \
style="margin:0in 0in 0.0001pt;font-size:11pt;font-family:Calibri,sans-serif"><span \
style="font-family:"Calibri Light",sans-serif"></span></p></div><div \
class="gmail_default" style="font-family:monospace,monospace"><br></div><div \
class="gmail_default" style="font-family:monospace,monospace">dvm.workarea_env \
on<br></div><div class="gmail_default" style="font-family:monospace,monospace">dvdb-# \
(sqf.sqf_runs.submitted_<wbr>changelist=dvm.workarea_env.<wbr>p4_changelist)<br>dvdb-# \
<b>inner join</b> dvm.dvm_events on<br>dvdb-# \
(dvm.workarea_env.wa_id = dvm.dvm_events.wa_id and dvm.dvm_events.type = \
'autosmoke')<br>dvdb-# where sqf.sqf_Runs.submitted_<wbr>shelvelist in \
(4404957,1277949);<br> sqf_id \
| sqf_sl | as_cl | wa_id | type \
<br>------------------------------<wbr>----------------------+-------<wbr>--+---------+-------+---------<wbr>--<br> \
arazhang_20.06.28-20:59:06_<wbr>soc_navi24_main@4404799 | 4404957 | 4405363 | 2772 \
| autosmoke<br>(1 row)<br></div><div class="gmail_default" \
style="font-family:monospace,monospace"><br></div><div class="gmail_default" \
style="font-family:monospace,monospace"><br></div><div class="gmail_default" \
style="font-family:monospace,monospace">Is there a way to retain the effect of that \
first outer join while enforcing that inner join \
?</div></div></blockquote><div><br></div><div>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.</div><div><br></div><div>David J.</div><div><br></div>
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic