MIME-Version: 1.0
References: 
 <CA+i5JwZ1y=KNn8uJr_DLe2LZCeLxVxY0osHBVoaELUVC=JWr6A@mail.gmail.com>
 <CA+i5JwZCt0c1V-LexLo50Oa+r_UVLM26ZQ4pqq2d9XYaBebKjA@mail.gmail.com>
 <CAJexoSJ3g9W1XtdUVnss+5cw=r-ek-JSfiGUaBxCGG=BJLmHQQ@mail.gmail.com>
 <CA+i5Jwb3q=dJ3LT0MmpFOhcxENuSkLV9Stv+fj5y6U=7HCgDCQ@mail.gmail.com>
In-Reply-To: 
 <CA+i5Jwb3q=dJ3LT0MmpFOhcxENuSkLV9Stv+fj5y6U=7HCgDCQ@mail.gmail.com>
From: Steve Midgley <science@misuse.org>
Date: Thu, 9 Jun 2022 16:25:27 -0700
Message-ID: 
 <CAJexoS++wfVFeqbZUJQZznP=-jtfba4MTZzA_Kz5aQCNL4eFWw@mail.gmail.com>
Subject: Re: A function to find errors in groups in a table
To: Shaozhong SHI <shishaozhong@gmail.com>
Cc: pgsql-sql <pgsql-sql@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="0000000000008d2df105e10c23a7"
Archived-At: 
 <https://www.postgresql.org/message-id/CAJexoS%2B%2BwfVFeqbZUJQZznP%3D-jtfba4MTZzA_Kz5aQCNL4eFWw%40mail.gmail.com>
Precedence: bulk

--0000000000008d2df105e10c23a7
Content-Type: text/plain; charset="UTF-8"

On Thu, Jun 9, 2022 at 4:13 PM Shaozhong SHI <shishaozhong@gmail.com> wrote:

> The first two can be regarded right.  Certainly, the last one must be
> wrong.  How can a primary flow get into a secondary flow?
>
> That is odd.
>
> Regards,
>
> David
>
> On Thursday, 9 June 2022, Steve Midgley <science@misuse.org> wrote:
>
>>
>>
>> On Thu, Jun 9, 2022 at 6:37 AM Shaozhong SHI <shishaozhong@gmail.com>
>> wrote:
>>
>>> There is a table full of grouped values like the following
>>>
>>> nodeid     link_type      primary
>>> 11           outflowlink       1
>>> 11              inflowlink      1
>>> 11              outflowlink     2
>>>
>>> Primary of 1 indicates a primary water course.  Primary of 2 indicates a
>>> secondary water course.
>>>
>>> Obviously, one of the out flow links is an error, as its primacy value
>>> is 2.  It is wrong that water flows from a primary water course into a
>>> secondary water course.
>>>
>>> How can a function can be designed to find and report such errors?
>>>
>>
>> Is the problem that there exists this row:
>>
>> 11              outflowlink     2
>>
>> But there is no corresponding row:
>>
>> 11              inflowlink     2
>>
>> So that you need to find all "orphan" rows that don't have a
>> corresponding member with the opposing data field inflowlink or
>> outflowlink where the "primary" column is the join field between the two
>> rows?
>>
>
You didn't answer my question, at least in a way I can understand, which
makes it hard to give you useful input on a solution.. Also, please try to
bottom post. This list asks posters to reply at the bottom of each email.

Steve

--0000000000008d2df105e10c23a7
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr"><br></div><br><div class=3D"gmail_quote">=
<div dir=3D"ltr" class=3D"gmail_attr">On Thu, Jun 9, 2022 at 4:13 PM Shaozh=
ong SHI &lt;<a href=3D"mailto:shishaozhong@gmail.com">shishaozhong@gmail.co=
m</a>&gt; wrote:<br></div><blockquote class=3D"gmail_quote" style=3D"margin=
:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"=
>The first two can be regarded right.=C2=A0 Certainly, the last one must be=
 wrong.=C2=A0 How can a primary flow get into a secondary flow?<div><br></d=
iv><div>That is odd.</div><div><br></div><div>Regards,</div><div><br></div>=
<div>David<br><br>On Thursday, 9 June 2022, Steve Midgley &lt;<a href=3D"ma=
ilto:science@misuse.org" target=3D"_blank">science@misuse.org</a>&gt; wrote=
:<br><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;bo=
rder-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir=3D"ltr"><di=
v dir=3D"ltr"><div><br></div></div><br><div class=3D"gmail_quote"><div dir=
=3D"ltr" class=3D"gmail_attr">On Thu, Jun 9, 2022 at 6:37 AM Shaozhong SHI =
&lt;<a href=3D"mailto:shishaozhong@gmail.com" target=3D"_blank">shishaozhon=
g@gmail.com</a>&gt; wrote:<br></div><blockquote class=3D"gmail_quote" style=
=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding=
-left:1ex"><div dir=3D"ltr"><div class=3D"gmail_quote"><div dir=3D"ltr" cla=
ss=3D"gmail_attr">There is a table full of grouped values like the followin=
g<br></div><div dir=3D"ltr"><div dir=3D"ltr"><div><br></div><div>nodeid=C2=
=A0 =C2=A0 =C2=A0link_type=C2=A0 =C2=A0 =C2=A0 primary</div><div><div>11=C2=
=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0outflowlink=C2=A0 =C2=A0 =C2=A0 =C2=
=A01</div><div>11=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 inflowlin=
k=C2=A0 =C2=A0 =C2=A0 1</div><div>11=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 outflowlink=C2=A0 =C2=A0 =C2=A02</div></div><div><br></div><div>=
Primary of 1 indicates a primary water course.=C2=A0 Primary of 2 indicates=
 a secondary water course.</div><div><br></div><div>Obviously, one of the o=
ut flow links is an error, as its primacy value is 2.=C2=A0 It is wrong tha=
t water flows from a primary water course into a secondary water course.</d=
iv><div><br></div><div>How can a function can be designed to find and repor=
t such errors?</div></div></div></div></div></blockquote><div><br></div>Is =
the problem that there exists this row:<div><br></div><div><font face=3D"mo=
nospace">11=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 outflowlink=C2=
=A0 =C2=A0 =C2=A02</font><br></div><div><br></div><div>But there is no corr=
esponding row:</div><div><br></div><div><font face=3D"monospace">11=C2=A0 =
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 inflowlink=C2=A0 =C2=A0 =C2=A02=
=C2=A0=C2=A0</font></div><div><br></div><div>So that you need to find all &=
quot;orphan&quot; rows that don&#39;t have a corresponding member with the =
opposing data field inflowlink or outflowlink=C2=A0where the &quot;primary&=
quot; column is the join field between the two rows?</div></div></div></blo=
ckquote></div></blockquote><div><br></div><div>You didn&#39;t answer my que=
stion, at least in a way I can understand, which makes it hard to give you =
useful input on a solution.. Also, please try to bottom post. This list ask=
s posters to reply at the bottom of each email.</div><div><br></div><div>St=
eve=C2=A0</div></div></div>

--0000000000008d2df105e10c23a7--