>
>Catlink has:
>ab_id catagory
>56 animal
>56 mineral
>57 animal
>58 animal
>58 mineral
>58 vegetable
>
>catagory has:
>animal
>vegetable
>mineral
>
>
>To get what you want you can use a LEFT OUTER JOIN as in;
>
>
>SELECT Catagory.Catagory, CatLink.Ab_id ;
> FROM Catagory LEFT OUTER JOIN CatLink ;
> ON Catagory.Catagory = CatLink.Catagory ;
> ORDER BY 2 ;
> INTO CURSOR MyResult
>
>GO TOP
>LOCATE FOR ab_id = 57
>
>* Now process the records until ab_id does not = 57.
>
>
>When you sue the where clause you are eliminating the records that don't have a 57 in Ab_id including those that have the null there. Don't filter in the select, rather filter after the select.
Jim,
The good place to filter inner table of an outer join is the ON portion of the FROM clause:
SELECT Catagory.Catagory, CatLink.Ab_id ;
FROM Catagory LEFT OUTER JOIN CatLink ;
ON Catagory.Catagory = CatLink.Catagory ;
and ab_id=52 ;
INTO CURSOR MyResult
It eliminates Fox-specific LOCATE and will work on the server.
In ANSI SQL-92 standard the outer join performs first and WHERE clause restricts result set.
Note that pre SQL-92 syntax works one way on SQL SERVER and SYBASE (performs WHERE first, than JOIN), another on ORACLE.
HTH
Oleg