Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Unexpected Join Results

Unexpected Join Results

Unexpected Join Results

parent table (p) has 569,275 records and child table (c) has 633,283

CODE -->

select count(*) from parent  -- 569,275
select count(*) from child  -- 633,283 

All parents have at least 1 child

CODE -->

select p.*  FROM [parent] p where p.pID  in (select cid from child)  -- 569,275 matches parent count 

Why does inner join return more rows than the total rows on the child. I expect the same as on the child (633,283) but instead it returned 172 rows more (633,455).

Any explanation why?

CODE -->

select p.*, c.*   FROM [parent] p
inner join child c on p.pid = c.cid  -- 633,455 

Other counts. Note, there is one child record not in the parent (orphan) but I don't think that would explain the additional 172 rows.

CODE -->

-- in child but not parrent
select cg.*  FROM chargep cg where cg.CGCASEID not in (select cmcaseid from [CASEMASP])  -- 1

-- in parent but not in child
select p.*  FROM [parent] p where p.pID not in (select cid from child)  -- 0

-- in parent and at least one matching child.  This matches parent count
select p.*  FROM [parent] p where p.pID  in (select cid from child)  -- 569,275 



RE: Unexpected Join Results

Just a guess here...

parent.pid looks like a Primary Key on parent table, but child.cid looks to me like a Primary Key on child table, and NOT a Foreign Key to parent table.

I would suspect the Foreign Key to parent table would be called something like child.pid

I am probably wrong... ponder

---- Andy

There is a great need for a sarcasm font.

RE: Unexpected Join Results


Unfortunate. That isn't it. This a legacy database imported from AS/400. There are no primary keys or foreign keys per se. The data is linked by fields containing business data. I masked the table an fields since it is company data but the data is linked by a caseid (case number) and in the parent table it is called CMCASEID and in the child it is called CGCASEID. Both contain the person's caseid. Not independent primary/foreign key as desired.

Obviously, breaking normalization rules. 1) no primary key, 2) data in more than one bucket.

As for the solution - still not sure why it is happening, but I may try a left join instead of inner join to see if that helps.


RE: Unexpected Join Results

That was just a shot in the dark...

“breaking normalization rules” – don’t ley me start about this issue! Story of my 20+ professional life as a programmer, drives me crazy.

Like you said, you may try left join and see. If that gives you what you need, you may try:

Select … inner join …
Select … left join …

Just to get those 172 offending records just to see what is the problem.

---- Andy

There is a great need for a sarcasm font.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close