Newbie creating inner join - works in test but not with live data!

[#1]

Hi. I'm a new user trying to join 3 tables to create a fourth in order to do some grouping and counting across the merged data.

The following code works great as a test:

test_Claim:
Load * Inline
[testcontactno
Contactno1
Contactno2
Contactno3];
test_ClaimDetail:
load * Inline
[testcontactno,testactionid
Contactno1,Action1
Contactno1,Action2
Contactno1,Action3
Contactno2,Action1
Contactno2,Action2
Contactno3,Action1];
test_Action:
Load * Inline
[testactionid,teststatus
Action1,OPEN
Action2,MIDDLE
Action3,CLOSED];
test_ClaimDetailAction:
noconcatenate load testcontactno resident test_Claim;
inner join load testcontactno,testactionid resident test_ClaimDetail;
inner join load testactionid,teststatus resident test_Action;
drop field testactionid;
[\code]
The results being:
<pre>

Contactno1 OPEN
Contactno2 OPEN
Contactno3 OPEN
Contactno1 MIDDLE
Contactno2 MIDDLE
Contactno1 CLOSED
[\code]
However, when I perform the exact same join on live data I get the result equivalent to:
<pre>

Contactno1 OPEN
Contactno2 OPEN
Contactno3 OPEN
[\code]
i.e. ALL claim numbers, with one single action against the lot.
I've tried to replicate the live problem in the test scenario by introducing:<ul><li>claims which have no matching claimdetails</li><li>orphaned claimdetails and actions</li><li>blank entries </li></ul>
to try to replicate the sort of errors in the live data that might be causing the issue, but all of these are handled fine and still give the correct result.
Any ideas/help gratefully received. </body>

[#2 · Accepted solution]

I did say I was a newbie didn't I?

I was looking in the table viewer preview to see the results of the join. The result seems to be ordered and grouped by the last table in the join, so all that was happening was that the entire preview was filled with the matches between the claims and the first action. There were so many it filled all available space in the preview - had I viewed the entire table I'd have seen there were other actions further down the list.

Sorry if I've wasted anyone's time!