view - multiple tables

[LenaS]

Here's what's happening. I am creating a view. I add the primary table. I add table2 for lookup of a code description. Seems to work fine so far. Then I add a third table for lookup of another code's description. When I run it, the description shown from table3 is always the same and not the correct one for the code in the primary table.Why is this not working? I've told it to left join on fields being equal.(A to B, A to C)I have several more lookup tables to add to this. Need your help.

 

[fluteplr]

Go to the view sql window in the view designer and cut out the sql code and paste it here so we can se what is going on.

 

[LenaS]

I've tried this several times. The table3 in this example works fine when it is table2. What's up with that?

SELECT Claims.*, Clmstatus.*, Examiner.examiner, Examiner.examinername;
FROM mrcdata!claims LEFT OUTER JOIN mrcdata!clmstatus;
LEFT OUTER JOIN mrcdata!examiner ;
ON Claims.examiner = Examiner.examiner ;
ON Claims.clmsts = Clmstatus.clmsts;
ORDER BY Claims.claim, Claims.clmsub

 

[ramani]

1. You have to use INNER JOIN instead of the LEFT OUTER JOIN.

2. By SQL code, you can put that as ....
SELECT Claims.*, Clmstatus.*, Examiner.examiner, Examiner.examinername;
FROM mrcdata!claims, mrcdata!clmstatus, mrcdata!examiner ;
WHERE Claims.examiner = Examiner.examiner AND ;
Claims.clmsts = Clmstatus.clmsts ;
ORDER BY Claims.claim, Claims.clmsub


Hope this helps
ramani :-9
(Subramanian.G)
FoxAcc
ramani_g@yahoo.com
LET KNOW IF THIS HELPED. ENOUGH EXPERTS ARE HERE TO HELP YOU OUT! BEST OF LUCK

 

[LenaS]

I keyed that into the command window and it works fine if there is always a match in every child table. I need all records from the primary even when some codes are blank and will not find a match on a lookup table. That's why I was using left outer.

By the way, I was using the view designer to create this. It doesn't allow one to modify from the SQL window. Also, when I select inner join there, it does not produce same results as your example.

Will I always have to create views this way?

 

[DEDMOD]

Have you tried creating a view with two of the tables and then using that view and a third table to create a second view? This process might be unwieldy with many tables, but at least it should either work or give you some idea where your'e going wrong.

Dave Dardinger