Re: matching rows differing only by fkey,pkey

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