Unexpected Join Results
Unexpected Join Results
(OP)
parent table (p) has 569,275 records and child table (c) has 633,283
All parents have at least 1 child
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?
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.
Thoughts?
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
Thoughts?
Jim
Talk To Other Members
RE: Unexpected Join Results
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...
---- 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.
Jim
RE: Unexpected Join Results
“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 …
MINUS
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.