Focal Point
Excluding records in one to many join

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

September 27, 2004, 12:12 PM
<Jonathan Sutter>
Excluding records in one to many join
I have two tables with a one to many relationship. For explanation purposes, let's say the primary table is CLAIM and the related table with multiple related records is CHARACTERISTIC. I need to work with a recordset that includes all CLAIM records that do NOT have a particular characteristic.

While I suspect the solution will involve mutliple steps, I haven't had any luck working with HOLD files. In any event, no matter how I think about it, I find myself needing to perform some sort of join that would return only those CLAIM records that do NOT have a match in the other table.

My limited experience is making this a tough nut to crack, though I expect the solution will turn out to be simple.
September 27, 2004, 12:54 PM
Steve C
I think your best bet is to use the MATCH command in this case. It is well described in the FOCUS manual. This command will easily get you the records that appear in your first file without any corresponding records in the second. Here is a sample syntax from the manual:

MATCH FILE EMPLOYEE
SUM LAST_NAME
BY EMP_ID
RUN
FILE EDUCFILE
SUM COURSE_CODE
BY EMP_ID
AFTER MATCH HOLD OLD‑NOT‑NEW
END

This syntax will yield the relationship you want.
September 27, 2004, 02:20 PM
<Jonathan Sutter>
I'm looking at the MATCH command as you suggest. Everything I'm seeing applies to MAINTAIN procedures. Will this work in other applications?
September 27, 2004, 03:53 PM
Leah
Match works with hold files as well. You want to ensure you get only those without the data, so a request might look as follows Note does not exclude multiple records necessarily, a proof of concept. I used the write to reduce to one record per key.

join....
table file xyz
where code eq 'abc'
print code
by key
on table hold as hold1
end
-*hold1 has all key records which have the child
table file xyz
where code ne 'abc'
write code
by key on table hold as hold2
end
-*hold2 has all the records not the child
match file hold2
write code
by key
run
file hold1
write code
by key
after match hold as hold3 old-not-new
end
-* hold3 has all the records in hold2 not in hold1
table file hold3
print code by key
end
September 27, 2004, 10:17 PM
susannah
Jonathan, read up on SET ALL = PASS, SET ALL=ON and SET ALL = OFF in (which manual? i can't find it)
You'll do your join
SET ALL = PASS
JOIN field in CLAIM TO ALL field IN CHARACTERISTIC AS J!1
then your filters, or screening statements, will be on the characteritic, which is in the GUEST file, not the host.
The setting ALL=PASS allows you to filter on the GUEST file. You don't use it if you're filtering on the HOST file.
IBI's Renee Teatro gives a great presentation on this subject, if you can arrange to get a copy, or see her at summit.
September 28, 2004, 12:34 AM
Piipster
SET ALL=PASS doesn't work on relational files.

If you are using an RDBMS you will want to do a MATCH.
September 28, 2004, 12:50 AM
Piipster
You can find MATCH explained in the Creating Reports With WebFOCUS Language Manual - Chapter 15 Merging Data Sources.
September 28, 2004, 08:18 PM
suzy_smith
You have discovered that there are 2 MATCH commands in Focus. One is used in MODIFY and MAINTAIN to maintain Focus databases. But the other is used in the reporting environment. It is extremely useful, and is more of a MATCH/MERGE where data can be thought of as being merged horizontally, rather than vertically.

HTH, Suzy
September 30, 2004, 01:48 PM
Noreen Redden
The problem that you are encountering is that there are multiple characteristics and you are looking for something that isn't there. Whether JOIN or MATCH, they work better when we are looking for something specific. In this case, if you go to the JOIN file and say show me anything that is NE 'ABC', and a particular claim has ABC and DEF, he will still show up on the report. So, here is my suggestion. First, create a HOLD file of the CLAIMS that do not have that characteristic (no matter how many characteristics they do have.
DEFINE FILE CHAR
FLAG/I5 = IF CHARACTERISTIC EQ '&VALUE' THEN 1 ELSE 0;
END
TABLE FILE CHAR
WRITE FLAG NOPRINT BY CLAIM_NO
WHERE TOTAL FLAG EQ 0
ON TABLE HOLD AS WHO_I_WANT
END

JOIN CLAIM_NO IN WHO_I_WANT TO CLAIM_NO IN CLAIMS AS JOIN1
END
TABLE FILE WHO_I_WNAT
PRINT claiminformation
END

(Faster if you can create that HOLD file in the same format as CLAIM (ie HOLD FORMAT DB2 ,etc.)

Alternatively, if there are not a lot of claims,
HOLD AS WHOIWANT FORMAT ALPHA
Then,
TABLE FILE CLAIM
PRINT Claiminformation
WHERE CLAIM_ID IN FILE WHOIWANT
END