pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Finding missing records...
3+ messages / 3 participants
[nested] [flat]

* Finding missing records...
@ 2000-02-25 15:56  Matthew Hagerty <matthew@venux.net>
  0 siblings, 2 replies; 3+ messages in thread

From: Matthew Hagerty @ 2000-02-25 15:56 UTC (permalink / raw)
  To: pgsql-sql

Greetings,

I have two tables that maintain a 1:1 relationship via an int based primary 
key.  A record in the primary table should never exist without an 
associated record in the secondary table, however somehow this has 
happened.  Can someone demonstrate a query that would show all records in 
the primary table that do not have associated records in the secondary table?

Thanks,
Matthew




^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: [SQL] Finding missing records...
@ 2000-02-25 16:11  Jan Wieck <wieck@debis.com>
  parent: Matthew Hagerty <matthew@venux.net>
  1 sibling, 0 replies; 3+ messages in thread

From: wieck@debis.com @ 2000-02-25 16:11 UTC (permalink / raw)
  To: Matthew Hagerty <matthew@venux.net>; +Cc: pgsql-sql

> Greetings,
>
> I have two tables that maintain a 1:1 relationship via an int based primary
> key.  A record in the primary table should never exist without an
> associated record in the secondary table, however somehow this has
> happened.  Can someone demonstrate a query that would show all records in
> the primary table that do not have associated records in the secondary table?

    SELECT att_pk FROM tab_pk WHERE NOT EXISTS
        (SELECT att_fk FROM tab_fk WHERE att_fk = att_pk);


    This is the SQL3 referential definition of PENDANT. It'll not
    be in the FOREIGN KEY support of 7.0, but we'll discuss if we
    implement  it  for 7.1 (even if it will be damned slooooow if
    done on every delete/update to an FK table).


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#========================================= wieck@debis.com (Jan Wieck) #





^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: [SQL] Finding missing records...
@ 2000-02-25 16:38  Sevo Stille <sevo@ip23.net>
  parent: Matthew Hagerty <matthew@venux.net>
  1 sibling, 0 replies; 3+ messages in thread

From: Sevo Stille @ 2000-02-25 16:38 UTC (permalink / raw)
  To: Matthew Hagerty <matthew@venux.net>; +Cc: pgsql-sql

Matthew Hagerty wrote:
 
> happened.  Can someone demonstrate a query that would show all records in
> the primary table that do not have associated records in the secondary table?

EXCEPT. As in "select key from table1 except select key_ref from
table2;". 

Sevo


-- 
sevo@ip23.net



^ permalink  raw  reply  [nested|flat] 3+ messages in thread


end of thread, other threads:[~2000-02-25 16:38 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-02-25 15:56 Finding missing records... Matthew Hagerty <matthew@venux.net>
2000-02-25 16:11 ` Jan Wieck <wieck@debis.com>
2000-02-25 16:38 ` Sevo Stille <sevo@ip23.net>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox