Message-ID: <40D86960.4070008@archonet.com>
Date: Tue, 22 Jun 2004 18:16:16 +0100
From: Richard Huxton <dev@archonet.com>
User-Agent: Mozilla Thunderbird 0.7 (X11/20040615)
MIME-Version: 1.0
To: Matthew Nuzum <matt@followers.net>
Cc: pgsql-sql@postgresql.org
Subject: Re: matching rows differing only by fkey,pkey
References: <200406221634.i5MGYcnb009716@ms-smtp-02.tampabay.rr.com>
In-Reply-To: <200406221634.i5MGYcnb009716@ms-smtp-02.tampabay.rr.com>
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit

Matthew Nuzum wrote:
> When the relationships are one to one or one to many this process is easy,
> however sometimes there's a many to many relationship.
> 
> It seems that a helpful tool would be a query that can return just the pkey
> of the original record copied from and the pkey of the newly created record.
> 
> For example, if the b table looked like this after a copy of 3 rows:
> bid | aid | field1 | field2 | field3
> 1   | 22  | abc    | 123    | abc123
> 2   | 22  | xyz    | 456    | xyz456
> 3   | 22  | pdq    | 789    | pdq789
> 4   | 23  | abc    | 123    | abc123
> 5   | 23  | xyz    | 456    | xyz456
> 6   | 23  | pdq    | 789    | pdq789
> 
> I'd like to get this:
> oldbid | newbid
> 1      | 4
> 2      | 5
> 3      | 6

SELECT
   one.bid AS oldbid,
   two.bid AS newbid
FROM
   b one,
   b two
WHERE
   one.field1=two.field1 AND ...
   AND two.bid > one.bid
;

Of course, if there are 3+ rows with duplicate field1/2/3 then this 
won't work.

-- 
   Richard Huxton
   Archonet Ltd