Unexpected Join Results

[jimoo]

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

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

All parents have at least 1 child

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?

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.

-- 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

Thoughts?



Jim

 

[Andrzejek]

Just a guess here...

parent.pid looks like a Primary Key on parent table, but child.[highlight #FCE94F]c[/highlight]id 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.[highlight #FCE94F]p[/highlight]id

I am probably wrong...


---- Andy

There is a great need for a sarcasm font.

 

[jimoo]

Andrzejek,

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.





Jim

 

[Andrzejek]

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:
[tt]
Select … inner join …
MINUS
Select … left join …
[/tt]
Just to get those 172 offending records just to see what is the problem.



---- Andy

There is a great need for a sarcasm font.