By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,659 Members | 1,678 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,659 IT Pros & Developers. It's quick & easy.

Query to find what is not there

P: n/a
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

Jul 23 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
On 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)
Jul 23 '05 #2

P: n/a


Thanks for teh quick reply.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Jul 23 '05 #3

P: n/a
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.

Jul 23 '05 #4

P: n/a

"srussell705" <sr******@lotmate.com> wrote in message
news:11*********************@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
Jul 23 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.