For some reason below inner join behaves like a left_outer join. I don't get why though...

-* Table with all cars by country
TABLE FILE CAR
PRINT CAR
BY COUNTRY
ON TABLE HOLD AS CARLEFT FORMAT FOCUS INDEX CAR
END

-* Same table, but BMW's are ugly
TABLE FILE CAR
PRINT CAR
BY COUNTRY
WHERE CAR NE 'BMW'
ON TABLE HOLD AS CARRIGHT FORMAT FOCUS INDEX CAR
END

-* Join them
JOIN
 INNER CARLEFT.SEG01.CAR IN CARLEFT TAG left TO UNIQUE CARRIGHT.SEG01.CAR
 IN CARRIGHT TAG right AS J0
 END
TABLE FILE CARLEFT
PRINT 
     'left.SEG01.COUNTRY' AS 'Left'
     'right.SEG01.COUNTRY' AS 'Right'
BY 'left.SEG01.CAR' AS 'Car'
ON TABLE SET PAGE-NUM OFF 
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     UNITS=CM,
     SQUEEZE=ON,
     ORIENTATION=LANDSCAPE,
$
TYPE=REPORT,
     GRID=OFF,
     FONT='TIMES NEW ROMAN',
     SIZE=10,
     CLASS=WFreport,
$
TYPE=DATA,
     BACKCOLOR=( RGB(221 221 221) RGB(231 231 231) ),
     CLASS=DATA,
$
ENDSTYLE
END

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

For the result... What I get is this:

Car        Left       Right
ALFA ROMEO ITALY      ITALY
AUDI       W GERMANY  W GERMANY
BMW        W GERMANY  
DATSUN     JAPAN      JAPAN
JAGUAR     ENGLAND    ENGLAND
JENSEN     ENGLAND    ENGLAND
MASERATI   ITALY      ITALY
PEUGEOT    FRANCE     FRANCE
TOYOTA     JAPAN      JAPAN
TRIUMPH    ENGLAND    ENGLAND

The line with BMW shouldn't be there, or should it?

Since you are using an INNER JOIN and referencing a field in the cross-referenced file, the BMW row should not be appearing. See http://techsupport.information...om/sps/43442037.html for an explanation on how it should work. This is how I've always known WebFocus/Focus INNER JOINS are supposed to work. But I used your code and could not get the row to disappear unless I put in a where statement to exclude blank car records. A work around that works, sure, but this is obviously a bug introduced since this article appeared. Unless someone else can point out something I've missed, you should open a case.

UNIQUE guarantees a host record to be returned regardless the cross-ref file. Change it to MULTIPLE would give you the inner result.

Isn't that just what left outer joins do?

The help files seem to contradict your statement as well:

quote:

When a report omits records that are not in both files, the join is called an inner join. When a report displays all matching records, plus all records from the host file that lack corresponding cross-referenced records, the join is called a left outer join.

And later on:

quote:

A unique, or one-to-one, join structure matches one value in the host data source to one value in the cross-referenced data source.

quote:

A non-unique, or one-to-many, join structure matches one value in the host data source to multiple values in the cross-referenced field.

Hua, after more testing, you are correct. Not sure how I got this wrong yesterday, but setting Wep's JOIN from UNIQUE to ALL/MULTIPLE removed the record since he is also referencing a field from the cross-reference file. Note, when the ALL setting is not used or set to OFF, it causes the JOIN to use an implicit INNER. Here's a small chart to show what removed the record (YES) and what didn't (NO) along with the impact of using the ALL setting.

SET ALL=                               {blank} ON    OFF  PASS
JOIN INNER   ... TO ALL/MULTIPLE   ...  YES    YES   YES  YES
JOIN INNER   ... TO {blank}/UNIQUE ...  NO     NO    NO   NO
JOIN {blank} ... TO ALL/MULTIPLE   ...  YES    NO    YES  NO
JOIN {blank} ... TO {blank}/UNIQUE ...  NO     NO    NO   NO

Now the Solution doc I reference above makes sense. When the Join is INNER and ALL/MULTIPLE and I remove the print for the cross-reference field. The BMW record appears

quote:

Isn't that just what left outer joins do?

I am as confused as you are! But I do know that "inner join" and "left-outer join" in Webfocus do not have the exact meaning as in English or as illustrated by Mr. Venn. These are the few factors I learned that would affect the join results:
globle setting, SET ALL=
local setting, inner/outer/blank
ALL/UNIQUE/MULTIPLE cross-reference file
and WHERE CLAUSE on columns of the cross-reference file will turn outer joins to inner joins!
DavSmith: Thanks for the layout. Do you mind if you give us the left-outer join permutations? I am very interested to have a sumary reference like yours, since every single report I write has join files.

With LEFT_OUTER's added:

SET ALL=                                  {blank} ON    OFF  PASS
JOIN INNER      ... TO ALL/MULTIPLE   ...  YES    YES   YES  YES
JOIN INNER      ... TO {blank}/UNIQUE ...  NO     NO    NO   NO
JOIN {blank}    ... TO ALL/MULTIPLE   ...  YES    NO    YES  NO
JOIN {blank}    ... TO {blank}/UNIQUE ...  NO     NO    NO   NO
JOIN LEFT_OUTER ... TO ALL/MULTIPLE   ...  NO     NO    NO   NO
JOIN LEFT_OUTER ... TO {blank}/UNIQUE ...  NO     NO    NO   NO

If I read that correctly, {blank}/UNIQUE never behaves like an INNER JOIN, but always as a LEFT_OUTER? That's thoroughly confusing.
It seems it's possible to define 1-{0..n} and 1-{1..n} relationships (LEFT_OUTER and INNER+MULTIPLE respectively), but not 1-1 relationships?

With regards to WHERE clauses in outer joins, the same happens in SQL actually. The reason in that case is because of 3-valued logic; if "right.CAR" is missing then "left.CAR EQ right.CAR" evaluates to NULL (or MISSING). That causes the WHERE clause to evaluate to false (which seems a somewhat arbitraty choice if you don't know whether a condition is valid or not for a given record).

When using JOIN on FOCUS db, I believe the original syntax to be easier to follow than the INNER/LEFT_OUTER syntax.

The reason is that it doesn't matter what you do, the child of a UNIQUE JOIN is ALWAYS deemed to exist, as Hua pointed out. If the segment instance does not exist, default values are applied.

So:

JOIN
  INNER CARLEFT.SEG01.CAR IN CARLEFT TAG left 
TO 
  UNIQUE CARRIGHT.SEG01.CAR IN CARRIGHT TAG right AS J0
END

is the same as

JOIN
  LEFT_OUTER CARLEFT.SEG01.CAR IN CARLEFT TAG left 
TO 
  UNIQUE CARRIGHT.SEG01.CAR IN CARRIGHT TAG right AS J0
END

For correct usage, ALL/MULTIPLE works

JOIN
  INNER CARLEFT.SEG01.CAR IN CARLEFT TAG left 
TO 
  MULTIPLE CARRIGHT.SEG01.CAR IN CARRIGHT TAG right AS J0
END

is different from

JOIN
  LEFT_OUTER CARLEFT.SEG01.CAR IN CARLEFT TAG left 
TO 
  MULTIPLE CARRIGHT.SEG01.CAR IN CARRIGHT TAG right AS J0
END

So the old syntax:

JOIN field [field2..field4] IN FILE [TAG tagname] TO [ALL] field IN FILE [TAG tagname] [AS joinname]

with:
SET ALL = OFF/ON/PASS

feels better to me, more in line with the FOCUS hierarchical heritage and gives as much control as is available and required.

Alan, I agree with you. For some of us wheezers, geezers, and sneezers, sometimes the OLD syntax makes more sense. For newer people never exposed to the OLD syntax, maybe the NEW syntax makes more sense since "SET ALL = {ON|OFF|PASS}" has no effect and is not needed when using INNER / LEFT_OUTER and MULTIPILE/UNIQUE.