Query to find what is not there

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?

TIAOn 30 Dec 2004 07:17:36 -0800, srussell705 wrote:

> 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

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)

(The first is probably more efficient)|||
Thanks for teh quick reply.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||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.|||"srussell705" <srussell@.lotmate.com> wrote in message
news:1104419856.430687.62340@.c13g2000cwb.googlegro ups.com...
> 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

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