Hi,
I have 3 DB tables A,B,C.A and B is having a same column and B and C is having a same column.How to Join these 3 tables?

This message has been edited. Last edited by: Kerry,

JOIN COLAB IN A TO COLAB IN B AS J1
JOIN COLBC IN A TO COLBC IN C AS J2

Danny,
That is ok.Actually,I am trying to JOIN 3 files ACT,GAN and SUM.The code which I am using is as:

JOIN ACT.REF_NUM IN ACT TO
GAN.CP_NBR IN GAN AS JN1
END
JOIN SUM.MOVE_NBR IN SUM TO
ACT.MOVE_NBR IN ACT AS JN2
END

When I am displaying the columns of ACT,it is showing me the columns of ACT and GAN.But when I am displaying the columns of SUM,it is showing the columns of SUM and ACT only.Ideally,it should show me the columns of all the 3 files.
Can anyone suggest what can be the problem?

You have to do it the way Danny said.

It is painfully obvious that you're a beginner, you've not done your homework or even tried to search this out. Next time, instead of asking someone to do your work for you, ask for how to search out the answer.

Go to IBI.COM and search their technical documentation library. If you want to do this in Developer Studio, enter WebFocus as the product. If you want to code it yourself, as it appears you do, enter FOCUS as the product. Don't worry about the release. This has not changed in years. In the SEARCH FOR box, enter JOIN.

And ask for training.

Hi mstanislov,
I understand what you said.I asked the question after doing my homework.But,the problem which i am facing is quite unusual.When I am trying to display the columns of ACT after JOINing it with GAN (using ? HOLD ACT),it is showing me the columns of both the tables ACT and GAN.Now,I joined this resultant table with SUM.So,it should also show me the columns of all the 3 tables ACT,GAN and SUM when i do ? HOLD SUM.But,it is only showing me the columns of SUM and actual columns of ACT only,not the original columns of GAN.
How is this possible? Any suggestion.

Thanks.

Can anyone reply plzz.

Shankar,

This is programming AND WebFOCUS 101:

  
JOIN ACT.REF_NUM  IN ACT TO GAN.CP_NBR IN GAN AS JN1
JOIN SUM.MOVE_NBR IN SUM TO ACT.MOVE_NBR IN ACT AS JN2

is illogical, the HOST file is ACT, not SUM:

JOIN ACT.REF_NUM  IN ACT TO GAN.CP_NBR   IN GAN AS JN1
JOIN ACT.MOVE_NBR IN ACT TO SUM.MOVE_NBR IN SUM AS JN2

Just as Danny gave you above.

The GUI only does what the "programmer" tells it to do, it's not magic...

Tom

Hi Tom,
Can't i use SUM as host file and ACT as reference file for second join which will be further used for reporting?

Thanks.

You get told how to do it by Danny, advice you seem to ignore or not follow.

Ginny points you back at Danny's post intimating that you re-read it. You obviously didn't.

Tom spells it out and I bet you still don't get it.

Another one to add to your ignore lists peeps

T

One last shot.

Shankar, if you do the joins as Danny and Tom have spelled out for you, once join1 is done, the MOVE_NBR in file B is logically part of file A. In order to bring file C into the join, the host of the 2nd join is logically file A.

Do the join as specified by Danny and Tom and then do a CHECK FILE ACT PICTURE. You will see the data as you want it. Then do your report and I'm sure that the answer will be correct.

There are 4 people telling you the correct way to do it. I think the odds are in our favor. Just give it a try.

Shankar, keep in mind that to join 3 tables together, you join the first two, then effectively add the third to the first.

The same goes with the forth, fifth, etc.

S,
If you are having problems understanding the JOIN structure - a picture might help.

Use the CHECK FILE command to see the logical file structure of your JOIN'd file.

a basic course sql would also help

your joining from 2 different parent files and will therefore never get the data in one request.

Frans, based on the criteria in the first post this join can be done the way Danny and Tom said in subsequent posts. You join A to B. Then you join A to C with a field from B. You then get A ==> B ==> C.

Ginny, I was pointing to Shankars 2nd post. he does this ? HOLD ACT with correct results and ? HOLD SUM with only SUM fields.

Of course it can be done by joining a to b to c... but only your parentfile will contain the joined fields. That's why I suggested a basic course in SQL

Unless I misunderstood, he is trying to join all 3 together and Danny's solution is the answer.

But the point is moot. Shankar seems to have left the building.

I got the idea.Thank you all.

He could be on that basic course ....



but I doubt it. Not all the time someone is prepared to give him the answer!!

T

Missing the columns in the 3rd file usually means the syntax of the join is incorrect.
-SET &ECHO=ALL
will show you what is wrong with the join. If you couldn't figure it out, the people here can quickly point out what is wrong if you post the executed codes/error messages.

Hope this will help.

Hua

I can see where the JOIN syntax could be confusing from someone coming from the SQL world.

In the SQL world you would always join A.COLAB to B.COLAB, then B.COLBC to C.COLBC (and C.COLCD to D.COLCD, etc). This is what Shankar was trying to do

As various folks have noted, in FOCUS, the 'from' side is always A.

EricH