Focal Point
Mutilple Join Question

This topic can be found at:
https://forums.informationbuilders.com/eve/forums/a/tpc/f/7971057331/m/6881023692

August 21, 2008, 04:30 AM
<ludo>
Mutilple Join Question
Hi all,

After using the search funciton I haven't found yet the answer to my question.

I'll try to explain my problem. I have two table : produit and tiers.
The first one contain two fields and I need to retrieve data from these fields in the second table.

Table produit:
IDPrdoduit
Emetteur (IDTiers)
Structureur (IDTiers)
...

Table Tiers
IDTiers
Nom
code
Rating
..

My join is

 
JOIN EMETTEUR IN PRODUIT TO IDTIERS IN TIERS AS J0
JOIN STRCUTUREUR IN PRODUIT TO IDTIERS IN TIERS AS J1
 


I tried unsuccessfully to output the field rating corresponding to the field emetteur and the one correspondign to the structureur


  
TABLE FILE PRODUIT
PRINT 
J0RATING
J1RATING
BY IDPRODUIT
END


But i have an error regarding the use of hte alias J0 and J1.

So how I could output the rating corresponding to my joined fields ?

Thx a lot

Ludo
August 21, 2008, 05:04 AM
nubi
do a search in help or on the forum for TAG...

basically you use the TAG command to alias(prefix) your fields whilst joining them but check the documentation for the exact syntax and usage.


Developer Studio 7.64
Win XP
Output: mostly HTML, also Excel and PDF

"Never attribute to malice that which can be adequately explained by stupidity." - Heinlein's Razor
August 21, 2008, 07:21 AM
Danny-SRL
Ludo,

Are you using Focus Files? If so, it should work. See the example below:
  
-* File ludo1.fex
TABLE FILE CAR
WRITE  
MAX.MODEL
BY SEATS
ON TABLE HOLD AS TIERS FORMAT FOCUS INDEX SEATS
END
DEFINE FILE CAR
EM_SEAT/I3=IF MODEL CONTAINS '4 DOOR' THEN 5 ELSE IF MODEL CONTAINS '2 DOOR' THEN 4 ELSE 2;
ST_SEAT/I3=IF MPG GT 20 THEN 5 ELSE IF MPG GT 15 THEN 4 ELSE 2;
END
TABLE FILE CAR
PRINT EM_SEAT ST_SEAT
BY COUNTRY BY CAR
ON TABLE HOLD AS PRODUIT FORMAT FOCUS
END
JOIN EM_SEAT IN PRODUIT TO SEATS IN TIERS AS M_
JOIN ST_SEAT IN PRODUIT TO SEATS IN TIERS AS T_
TABLE FILE PRODUIT
PRINT M_MODEL T_MODEL
BY COUNTRY BY CAR
END



Daniel
In Focus since 1982
wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF

August 21, 2008, 07:59 AM
<ludo>
Hi thx for your reply.

Is it only works with focus file ? In fact I'm using Oracle Tables ... shall I hold these tables onto a focus file as you did in you example ?

Ludo
August 21, 2008, 08:28 AM
nubi
did you try the TAG? that should work regardless of source


Developer Studio 7.64
Win XP
Output: mostly HTML, also Excel and PDF

"Never attribute to malice that which can be adequately explained by stupidity." - Heinlein's Razor
August 21, 2008, 08:56 AM
<ludo>
nubi,

I'm looking for it but I can find the right sample as the one of Danny-SRL.
August 21, 2008, 09:07 AM
Danny-SRL
Ludo,

Here is the TAG syntax:
  
-* File ludo2.fex
TABLE FILE CAR
WRITE
MAX.MODEL
BY SEATS
ON TABLE HOLD AS TIERS FORMAT FOCUS INDEX SEATS
END
DEFINE FILE CAR
EM_SEAT/I3=IF MODEL CONTAINS '4 DOOR' THEN 5 ELSE IF MODEL CONTAINS '2 DOOR' THEN 4 ELSE 2;
ST_SEAT/I3=IF MPG GT 20 THEN 5 ELSE IF MPG GT 15 THEN 4 ELSE 2;
END
TABLE FILE CAR
PRINT EM_SEAT ST_SEAT
BY COUNTRY BY CAR
ON TABLE HOLD AS PRODUIT FORMAT FOCUS
END
JOIN EM_SEAT IN PRODUIT TAG P TO SEATS IN TIERS TAG E AS M_
JOIN ST_SEAT IN PRODUIT TAG P TO SEATS IN TIERS TAG S AS T_
TABLE FILE PRODUIT
PRINT E.MODEL S.MODEL
BY COUNTRY BY CAR
END



Daniel
In Focus since 1982
wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF

August 21, 2008, 10:02 AM
<ludo>
Allrigth it seems to work

Thanks
August 21, 2008, 11:15 AM
Leah
quote:
J0RATING
J1RATING

As J0 and 1 weren't 'tags', (but you do need a period 'J0.' if they were), you can also use the table name followed by a period. BUT IN YOUR CASE you need the tags as you are doing a join on different fields to the same table


Leah