From: rihad@stream.az
To: David Johnston <polobo@yahoo.com>
Cc: 'Rihad' <rihad@stream.az>
Cc: pgsql-sql@postgresql.org
Subject: Re: Need help building this query
Date: Fri, 22 Jun 2012 05:56:30 -0000
Message-ID: <103e1a9e6628fa20819860cb6575a986.squirrel@mail.azuni.net> (raw)
In-Reply-To: <00e401cd4fdd$589b1e60$09d15b20$@yahoo.com>
References: <4FE35E88.6010805@stream.az>
<00e401cd4fdd$589b1e60$09d15b20$@yahoo.com>
> You seem to be describing a straight reconciliation between two tables.
> My
> current means of doing this are programmatically but for the simple case
> pure SQL should be doable. The main thing is that you have to distinguish
> between "duplicate" records first and then match them up:
>
> TableA Keys:
>
> AA
> AA
> AA
> AB
> AB
> AC
>
> TableB Keys:
> AA
> AA
> AB
>
> First you use "ROW_NUMBER() OVER (PARTITION BY key)" to assign an integer
> "sub-id" to every set of possible keys in both tables:
>
> TableA-Sub:
> AA-1
> AA-2
> AA-3
> AB-1
> AB-2
> AC-1
>
> TableB-Sub:
> AA-1
> AA-2
> AB-1
>
> Now, with these newly constructed key+sub-key values in place, you can
> perform a simple LEFT (or possibly FULL) JOIN between tables A & B.
>
> This makes no allowances for any of kind of desired date restriction on
> the
> matching nor does it consider the eventual report that you wish to
> generate.
> What this gives you is a listing of ALL rows in both tables with matched
> records joined together into a single (NULL-less) row while unmatched
> records will have one of the two resultant columns NULLed
>
> SELECT tableA.subid_a, tableB.subid_b
> FROM tableA FULL OUTER JOIN tableB ON (tableA.subid_a = tableB.subid_b)
>
> Requires at least version 8.4
>
This looks really promising, thanks. I'll read more about it (WINDOWING in
general) and see how it fits our model. This might allow me to distinguish
"operation" rows that exist in both TableA and TableB, and whether TableA
has more of them, in which case they would be marked unmatched. Using
sub-selects this seems doable.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: rihad@stream.az, polobo@yahoo.com
Subject: Re: Need help building this query
In-Reply-To: <103e1a9e6628fa20819860cb6575a986.squirrel@mail.azuni.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox