>The following SQL statement sometimes gives strange results- some of the INV fields will come back as .NULL. while others have values. The INV table does not contain .NULL. values. Is there something wrong with the syntax of the statement? I am using VFP 7. Thanks!
>
>SELECT Whocust.wacct, sales.*,inv.miles,inv.whoseller,inv.color,inv.doors,inv.odometer, ;
>inv.new_used,inv.decal,inv.county,inv.color;
>FROM cardata!whocust LEFT OUTER JOIN cardata!sales;
>LEFT OUTER JOIN cardata!inv ;
>ON Inv.line = Sales.line ;
>ON Whocust.wacct = Sales.sacct;
>WHERE Whocust.wacct = myacct ;
>ORDER BY sales.date, sales.make, sales.model ;
>into cursor vwsinv
OUTER JOINs will cause .NULL.s to appear when there are no matching records in one of your tables.
CREATE TABLE tbl1 (iTbl1Key I, cTbl1Dat C(10))
CREATE TABLE tbl2 (iTbl2Key I, cTbl2Dat C(10))
INSERT INTO tbl1 VALUES (1, "1")
INSERT INTO tbl1 VALUES (2, "2")
INSERT INTO tbl2 VALUES (1, "A")
SELECT cTbl1Dat, cTbl2Dat ;
FROM tbl1 INNER JOIN tbl2 ;
ON tbl1.iTbl1Key = tbl2.iTbl2Key
SELECT cTbl1Dat, cTbl2Dat ;
FROM tbl1 LEFT OUTER JOIN tbl2 ;
ON tbl1.iTbl1Key = tbl2.iTbl2Key
That make sense?