I recently found out that a colleague had a mainframe FOCUS program with a many-to-one JOIN that she'd been using for several years. From reading my 7.0 FOCUS manual, I'd always thought that only one-to-one and one-to-many JOINS were valid (in other words, each record in the host file had to be unique based on the JOIN fields used). Unless I misunderstood the text, the manual doesn't state that a many-to-one JOIN won't work but it only mentions using the one-to-one and one-to-many joins. I tested out a many-to-one JOIN on mainframe FOCUS 7.1.1 using a DB2 table as the host file and an indexed FOCUS format hold file as the target file, thinking that it probably wouldn't work correctly but it seemed to work just fine. I'm a little wary of using one, though, as I can't find it documented as a valid JOIN.

I did find a post where a forum member that noted the way a JOIN works when the host and targets have different formats is that it reads in a host record and finds associated records in the target file before moving on to the next host record and finding the next record's associated target file records. If this is correct, then I could see how a many-to-one JOIN just might work. Does anyone know if a many-to-one JOIN can be used with reliable results? Does anyone know of any documentation that says it should not be used?


Chris

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

Maybe someone has stronge technical background can explain how joins are architectured.

Logically, this type of join is commonly used to join transactions files to master files. And the results are very reliable.

Many to one doesn't work well for me most of the times. If there is that kind of join I prefer MATCH. I guess Masters would provide detaield info..thank u..

There is no intrinsic reason why a many-to-one join shouldn't work. I have used them often, when appropriate or necessary. The best way to ensure reliable results, as you discovered, is to make certain the target file is indexed on the join-to field/column.

A many to oner join IS a i=one to one join. There just happens to be duplicate values in the FROM file that match the same value in the TO file.

There is nothing special about many-to-one.

Ditto with OPALTOSH, but as Dan says, the target join column MUST be indexed, or in an RDBMS.

Or the two files/table must be sored in he same order.

That's great news. I'll be sure to always use an indexed target join field or a column in an RDBS when I use one.

Thanks to everyone for your help!

I was so focused ( ) on my original question that, until I got an answer, I didn't think about asking if many-to-many joins could also be valid if 'TO ALL ' is used. Any thoughts on this one?

(If I should have posted this to a new question, please let me know and I'll do so).

Thanks!

Remember when summing a numeric field in the crossreference file you can experience the "multiplicative effect". For each occurence of the host record it will retrieve the crossreference record and add them together. Use any prefix operator on the crossreferenced field to stop the multiplicative effect.

The same rules apply for many to many.

Its the same an a bundle of one to many joins.

I've stumbled many times with many-many joins, when I didn't have enough join-fields from both files to make them 1:n or n:1 joins. The results are not always as expected depand on what the selection criteria. The report could be working for months and all of sudden it is wrong. I think it is because n:n gives false match rather than missing on a left-outer joins, and you couldn't filter with missing records. So I have to have additional passthrus on one or both files to make sure 1:n or n:1 joins. Am I missing something?

Hua