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