Please help: Many to Many

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 (print

rint, sum

rint, print:sum, sum:sum) seem to capture this. Any help appreciated.

drew billingslea

Silver Member

posted

Hide Post

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

Posts: 46 | Location: San Francisco, California | Registered: April 14, 2003

Ignored post by drew billingslea posted

Show Post

posted

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.

drew billingslea

Silver Member

posted

Hide Post

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

Posts: 46 | Location: San Francisco, California | Registered: April 14, 2003

Ignored post by drew billingslea posted

Show Post

posted

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.

EricH

Platinum Member

posted

Hide Post

Try

SET CARTESIAN = ON

EricH

Posts: 164 | Registered: March 26, 2003

Ignored post by EricH posted

Show Post

posted

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

Please Wait. Your request is being processed...

Read-Only Topic

Focal Point

Focal Point Forums

WebFOCUS/FOCUS Forum on Focal Point

Please help: Many to Many

Focal Point | Privacy Statement | Terms of Service