Re: Need help building this query

> 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.