I have a 1:1 relationship between tables and am finding that the parent
is sometimes mising the child.
How do I query for what is not there?

TIA

Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are. Sample data is also a good idea, along with clear
specifications. Here is a wild guess based nothing you told us.

1) Clean up the orphans you have now.

DELETE FROM Children
WHERE NOT EXISTS
(SELECT *
FROM Parents AS P1
WHERE P1.parent_id = Children.parent_id);

2) Get a correct schema that enforces your busines rules with DRI
actions, like this:

CREATE TABLE Parents
(parent_id INTEGER NOT NULL PRIMARY KEY,
....);

CREATE TABLE Children
(parent_id INTEGER NOT NULL
REFERENCES Parents (parent_id)
ON UPDATE CASCADE
ON DELETE CASCADE,
child_id INTEGER NOT NULL,
PRIMARY KEY (parent_id, child_id),
....);

Remeber that is it never enough to mop the floor; you must also fix the
leak.

[quoted text, click to view]

SELECT * FROM ParentTbl
WHERE NOT EXISTS (
SELECT * FROM ChildTbl
WHERE ChildTbl.ParentKey = ParentTbl.ParentKey
)

or

SELECT * FROM ParentTbl
WHERE ParentTbl.ParentKey NOT IN (
SELECT ChildTbl.ParentKey FROM ChildTbl)

Thanks for teh quick reply.

*** Sent via Developersdex http://www.developersdex.com ***

[quoted text, click to view]

Not quite on topic, but I'm always wary of 1:1 relationships. Most of the
places I've seen them, there was such close affinity between the two tables
that they were either the same thing or one could be folded into the other.
Hmm?

Rich