Finding orphans and "childless parents" in mysql tables

I'm using mysql 4.0.10 (no subqueries until 4.1).

I have a master table containing a few million rows and a child table about 3x larger.
The association is many-to-1, so each row in the master table is associated with 1 to N rows in child.
All the IDs are non-null.

table MOM:
parent_ID, example {1,2,3,4,6}
more stuff...

table KID:
child_ID,
parent_ID, example {1,3,3,1,3,4,5,6,7}
more stuff...

I need a query to find the orphans in KID, and another one to find the ones in MOM that have no "children" in KID.
Something to find the orphans:
-> returns the ones with parent_ID=5 and 7 because it's not in MOM

Something to find the rows im MOM noone is connected to
-> returns the one with parent_ID=2, because it's not in KID

I'm using mysql 4.0.10 so subqueries like:
select *  from KID where parent_ID not in  (select parent_ID from MOM);
are not allowed - how can I do this with a JOIN or something else?
LVL 4
byttaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

virmaiorCommented:
SELECT * FROM KID LEFT JOIN MOM ON KID.parent_ID = MOM.parent_ID WHERE MOM.parent_ID IS NULL

that will show you the orphaned children

SELECT * FROM MOM LEFT JOIN KID ON MOM.parent_ID = KID.parent_ID WHERE KID.parent_ID IS NULL

and  the bereaved mothers
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
byttaAuthor Commented:
Silly me...

I saw this here:
http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_20333988.html
and here:
http://www.experts-exchange.com/Databases/Mysql/Q_21142426.html

Then I wrote ".... WHERE KID.parent_ID = NULL"

Using "=" instead of "IS" gives an empty set, so I was sure this wouldn't work...

Easy points to virmaior...
0
virmaiorCommented:
I made the same mistake a while ago, now I'll never forget (IS NULL) !=  (= NULL)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.