Hi, I'm trying to do a Many:Many LEFT outer join. So, if I have tables T1 and T2:

T1:
A 1
A 2
C 1

T2:
A "x1"
A "x2"
A "x3"
B "x1"

I want the result to be:
A 1 "x1"
A 1 "x2"
A 1 "x3"
A 2 "x1"
A 2 "x2"
A 2 "x3"
C 1 " "
None of the MATCH relationships (printrint, sumrint, print:sum, sum:sum) seem to capture this. Any help appreciated.

you might try:

SET ALL=ON
JOIN KEY IN T1 TO ALL KEY IN T2 AS J1_
TABLE FILE T1
PRINT VAL1 VAL2
BY KEY
BY VAL1 NOPRINT
BY VAL2 NOPRINT
END

the output is:

PAGE 1


KEY VAL1 VAL2
--- ---- ----
A 1 X1
1 X2
1 X3
2 X1
2 X2
2 X3
C 1 .

and the masters:

FILENAME=T1,SUFFIX=FOC
SEGNAME=TOP,SEGTYPE=S2
FIELDNAME=KEY,,A1,FIELDTYPE=INDEX,$
FIELDNAME=VAL1,,I5,$

FILENAME=T2,SUFFIX=FOC
SEGNAME=TOP,SEGTYPE=S2
FIELDNAME=KEY,,A1,FIELDTYPE=INDEX,$
FIELDNAME=VAL2,,A5,$

hth,

drew

Drew, thanks for the reply. Unfortunately, this doesn't seem to work because joins do not support many to many relationships. The first "A" in T1 matches all 3 entries in T2, but the second "A" does not get paired up with anything.

i made a slight change to my code:

SET ALL=ON
JOIN KEY IN T1 TO ALL KEY IN T2 AS J1_
TABLE FILE T1
PRINT KEY VAL1 VAL2
BY KEY NOPRINT
BY VAL1 NOPRINT
BY VAL2 NOPRINT
END

to better demonstrate the data.


PAGE 1


KEY VAL1 VAL2
--- ---- ----
A 1 X1
A 1 X2
A 1 X3
A 2 X1
A 2 X2
A 2 X3
C 1 .


and here is the t1 data:

PAGE 1


KEY VAL1
--- ----
A 1
A 2
C 1

and t2 data:

PAGE 1


KEY VAL2
--- ----
A X1
A X2
A X3
B X1


the other thing you might try is SQL passthru with a left outer join. That works for SQL or FOCUS datasources.

hth,

drew

Drew, thanks again for the reply. I must be on an older version of webFOCUS - I'm just not getting the output that you're getting (true to some IBI document on JOINs that I found, many:many is not working with JOINs. It finds a match for the first entry, say, "A", but since it never backs up, it prints a blank when tryin to match up the second instance of "A"). The only thing that SET ALL=ON is doing for me is, it mimics a left outer join by including parent instances with no child instances, but it still is unable to force a many:many join. Also, like you've said, I tried doing this by inserting straight SQL, but my version of webFOCUS does not seem to like it - I get an error on anything other than a regular (inner) join.

I saw a document that talked about MATCH supporting many:many, although I haven't been able to get it to work. Any ideas on how to use that? Thanks a lot.

Try

SET CARTESIAN = ON

EricH

Thanks, but that didn't work either =(
Any ideas on how to maybe try using MATCH to get a many:many ? Thanks.