Skip site navigation (1) Skip section navigation (2)

Re: matching rows differing only by fkey,pkey

From: Richard Huxton <dev(at)archonet(dot)com>
To: Matthew Nuzum <matt(at)followers(dot)net>
Cc: pgsql-sql(at)postgresql(dot)org
Subject: Re: matching rows differing only by fkey,pkey
Date: 2004-06-22 17:16:16
Message-ID: 40D86960.4070008@archonet.com (view raw or flat)
Thread:
Lists: pgsql-sql
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

In response to

pgsql-sql by date

Next:From: Bruno Wolff IIIDate: 2004-06-22 17:27:04
Subject: Re: matching rows differing only by fkey,pkey
Previous:From: Matthew NuzumDate: 2004-06-22 16:34:35
Subject: matching rows differing only by fkey,pkey

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group