Focal Point
PLEASE help: MATCH one-to-many problem

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

September 30, 2004, 03:08 AM
<bigpgo>
PLEASE help: MATCH one-to-many problem
I'm trying to get the equivalent of a Left-Outer join in webfocus by doing MATCH with keyword "OLD". My 2 tables are A and B.

Table A: (ProjID = key):

ProjID ProjName ProjPhase
------ -------- ---------
1 bbq Phase1
2 bbq Phase1
3 bbq Phase2

Table BFrownerProjectName+ProjectPhase = key):

ProjectName ProjectPhase ProjInfo
----------- ------------ ---------
bbq Phase1 good
bbq Phase2 bad

Then I do:

MATCH FILE A
PRINT
ProjID
BY ProjName
BY ProjPhase
RUN

FILE B
PRINT
ProjectInfo
BY ProjectName AS ProjName
BY ProjectPhase AS ProjPhase
AFTER MATCH HOLD AS myReport OLD
END

TABLE FILE myReport ..
PRINT
ProjName ProjPhase ProjectInfo
..
END

This produces:
bbq Phase1 good
bbq Phase1 ''
bbq Phase2 bad

Note that it never finds a match for the 2nd record - the info is blank!!!
The match seems to assume it's a 1-to-1 relationship, and as soon as it finds a match in table B for the pair "bbq/Phase1", it stops trying to look for additional matches. Why??

I found this documentation online:

"If one set of sort fields is a subset of the other, then it�s a one-to-many merge (all that match.)OTHERWISE it�s a one-to-one".

Well, [ProjName, ProjPhase] is a subset of [ProjName, ProjPhase]. In fact, I tried throwing in some additional useless BY sort fields but I'm still getting 1-to-1. Any ideas? Thanks a lot.
September 30, 2004, 12:22 PM
Roland
Using MATCH FILE is not only a question of MATCH-Phrase and BY columns, it is also a question of using the right verbs. In TechSupport Knowledge Base: Fine Tuning MATCH Processing you can find more about all this.

Have fun ! Wink
September 30, 2004, 03:52 PM
susannah
Big,
Use SUM, not Print, for the second part of the match.
However if you want to end up with records than there are currently in File A, then reverse the order of the match, and use HOLD NEW
September 30, 2004, 09:18 PM
<bigpgo>
Thank you - using SUM for the 2nd one did the trick!