Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1nzNdy-0007o2-SD for pgsql-sql@arkaria.postgresql.org; Thu, 09 Jun 2022 19:16:34 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1nzNdx-00088E-6w for pgsql-sql@arkaria.postgresql.org; Thu, 09 Jun 2022 19:16:33 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1nzNdw-000885-SP for pgsql-sql@lists.postgresql.org; Thu, 09 Jun 2022 19:16:32 +0000 Received: from premium22-2.web-hosting.com ([68.65.122.104]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1nzNdt-0004x0-Aw for pgsql-sql@lists.postgresql.org; Thu, 09 Jun 2022 19:16:32 +0000 DKIM-Signature: v=1; a=rsa-sha256; q=dns/txt; c=relaxed/relaxed; d=misuse.org; s=default; h=Content-Type:Cc:To:Subject:Message-ID:Date:From:In-Reply-To: References:MIME-Version:Sender:Reply-To:Content-Transfer-Encoding:Content-ID: Content-Description:Resent-Date:Resent-From:Resent-Sender:Resent-To:Resent-Cc :Resent-Message-ID:List-Id:List-Help:List-Unsubscribe:List-Subscribe: List-Post:List-Owner:List-Archive; bh=1EoCJgWVDDcJYT7gLoDGdrWhHOP2/8CFVLG+Q7jDOh8=; b=IEsDghaN1uIQ9r7153t4+XJFbV +ce5rD7GALWNTUaSBmsSgBFXi1pzrJRHSQ71+6Vaa4fY3/hW9xObDGPgK2qTvAZO1Pb2hS7jLaTaM y4Gknpqr4d9TBlIGNf9fi+rKlmzF9NS1lWn4rKBpjlPLp8y9q8+s9D9x6EKgKmBLPHLxzKgB/vP0a pjC/oj1VIcK4QSdhYexcB9VJ05yw6t78ChTH+SZ6BVJPgSHys0x4aD6tEfzNwViWQC+5/OqKFMSIu IjYcr0uGzIFfpW3ppNKipUZHFLyLJQghiCltxMJww4VCpzDMUYi6if/0x+R85DBj0583XAKgINujA QKrOB9WQ==; Received: from mail-pf1-f178.google.com ([209.85.210.178]:33681) by premium22.web-hosting.com with esmtpsa (TLS1.2) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.95) (envelope-from ) id 1nzNdm-0081Ho-4D for pgsql-sql@lists.postgresql.org; Thu, 09 Jun 2022 15:16:26 -0400 Received: by mail-pf1-f178.google.com with SMTP id w21so21937676pfc.0 for ; Thu, 09 Jun 2022 12:16:21 -0700 (PDT) X-Gm-Message-State: AOAM531CMCwNJNsnLcsjiKRTAYpUTIPbbTOiEvuYvTPzHjyLIMJKg9Qo vLwWgf5E0zuNzb7y2WjQjKgLLKKgay0j4xtrIUY= X-Google-Smtp-Source: ABdhPJxb38NAy8IfpFJjwtY8qZ0KVCjzKNBtvG03MvpZD3xjjB1F6+m6zRvS5agfUd5+WNiAWxNnN7onPXNLCKQAg44= X-Received: by 2002:a62:8101:0:b0:51b:b859:7043 with SMTP id t1-20020a628101000000b0051bb8597043mr41868016pfd.25.1654802181183; Thu, 09 Jun 2022 12:16:21 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Steve Midgley Date: Thu, 9 Jun 2022 12:16:12 -0700 X-Gmail-Original-Message-ID: Message-ID: Subject: Re: A function to find errors in groups in a table To: Shaozhong SHI Cc: pgsql-sql Content-Type: multipart/alternative; boundary="0000000000002edfb205e108a8aa" X-AntiAbuse: This header was added to track abuse, please include it with any abuse report X-AntiAbuse: Primary Hostname - premium22.web-hosting.com X-AntiAbuse: Original Domain - lists.postgresql.org X-AntiAbuse: Originator/Caller UID/GID - [47 12] / [47 12] X-AntiAbuse: Sender Address Domain - misuse.org X-Get-Message-Sender-Via: premium22.web-hosting.com: authenticated_id: science@misuse.org X-Authenticated-Sender: premium22.web-hosting.com: science@misuse.org X-Source: X-Source-Args: X-Source-Dir: X-From-Rewrite: unmodified, already matched List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000002edfb205e108a8aa Content-Type: text/plain; charset="UTF-8" On Thu, Jun 9, 2022 at 6:37 AM Shaozhong SHI 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? Thanks for clarifying your problem, Steve --0000000000002edfb205e108a8aa Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable


On Thu, Jun 9, 2022 at 6:3= 7 AM Shaozhong SHI <shishaozho= ng@gmail.com> wrote:
There is a table full of grouped values like the followi= ng

nodeid=C2= =A0 =C2=A0 =C2=A0link_type=C2=A0 =C2=A0 =C2=A0 primary
11=C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0outflowlink=C2=A0 =C2=A0 =C2=A0 =C2= =A01
11=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 inflowlin= k=C2=A0 =C2=A0 =C2=A0 1
11=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 outflowlink=C2=A0 =C2=A0 =C2=A02

= Primary of 1 indicates a primary water course.=C2=A0 Primary of 2 indicates= a secondary water course.

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.

How can a function can be designed to find and repor= t such errors?

Is = the problem that there exists this row:

11=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 outflowlink=C2= =A0 =C2=A0 =C2=A02

But there is no corr= esponding row:

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

So that you need to find all &= quot;orphan" rows that don't have a corresponding member with the = opposing data field inflowlink or outflowlink=C2=A0where the "primary&= quot; column is the join field between the two rows?

Thanks for clarifying your problem,
Steve=C2=A0
--0000000000002edfb205e108a8aa--