Inner Join Issues
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 284
- Joined: Fri Oct 13, 2006 4:31 am
Inner Join Issues
I am performing an inner join of two tables. The left table contains only 216 records while the right table contains 5 million records.
However, I am getting output as 1896 records in the output.
We should ideally get only 216 records because thats what is there in the left table. Why is this happening?
However, I am getting output as 1896 records in the output.
We should ideally get only 216 records because thats what is there in the left table. Why is this happening?
Didn't we just go thru this recently? Nothing about doing an inner join ensures you only get out as many rows as exist on one side of the join. It's all about the keys and data in both tables. It is about multiple matches on the 'right' table in your case.
Last edited by chulett on Wed Feb 13, 2008 9:25 am, edited 1 time in total.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 284
- Joined: Fri Oct 13, 2006 4:31 am
Two things I left out to mention:
The right table which has 5 million records has no duplicates for the key combination which I mentioned in the join.
Secondly, I fired the same inner join query in db2 which returned exactly 216 records.
So when we do a inner join on these tables via a SQL query you get 216 rows .
You create a PX job and u get 1896 records..this beats me
The right table which has 5 million records has no duplicates for the key combination which I mentioned in the join.
Secondly, I fired the same inner join query in db2 which returned exactly 216 records.
So when we do a inner join on these tables via a SQL query you get 216 rows .
You create a PX job and u get 1896 records..this beats me
Please describe your design: Are you talking about User-SQL or a Join stage? In your Join stage, how are you matching the keys (could leading spaces or zeroes be the difference)?
Kenneth Bland
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
-
- Participant
- Posts: 284
- Joined: Fri Oct 13, 2006 4:31 am
I am using Join stage. there are two inputs to the join stage. Both inputs are ODBC stages. Both have a simple select statement with order by.
All the primary keys have been added to join stage.
There are no duplicates in the right table. In the left table there are duplicates but total number of rows is 216. For every row in left table there is one and only one match in the right table.
All the primary keys have been added to join stage.
There are no duplicates in the right table. In the left table there are duplicates but total number of rows is 216. For every row in left table there is one and only one match in the right table.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Participant
- Posts: 284
- Joined: Fri Oct 13, 2006 4:31 am
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Participant
- Posts: 284
- Joined: Fri Oct 13, 2006 4:31 am
I removed duplicates from the left table which had 216 records...Now the output of join is as expected i.e equal to number of records in left table.
But it still is a puzzle that when there were 216 records why the output had 1896 records. Say even if there are duplicates you should get only those number which you have in left table
?
But it still is a puzzle that when there were 216 records why the output had 1896 records. Say even if there are duplicates you should get only those number which you have in left table
?