>Suppose table3 has 3 records for one of the reps in table1 & table2
using the clause:
>From table1 t1 left join table2 t2 on t1.f1=t2.f2 left join table3 t3 t1.f1=t3.f3
where t1.f1=date() AND t2.f2=date(); ..

>gives ok results when there are no matching records in table3, but if there are 3 records in table3, it'll give 3 times the counts for t1.f1, etc

Ok this part is due to the basic SQL behavior, and is known as a cartesian join. An SQL Select will attempt to join all records in one table with all records in second (so result is recno(t1) * recno(t2). So simply saying SELE t1.field1, t1.field2, t2.field3, t2.field4 FROM t1, t2 will give you a matching of all recs in t1 against all recs in t2--probably not what you are looking for.

When you do a three way outer join like you are doing above, you end up with a result set (all of the records from t1, and the matching fields from t2. any t2's that don't match will have the t2 fields included with .NULL. in their properties, and then used in the same way with table 3.

Without seeing the specific example you are working I can't be sure, but I suspect the use of left joins is the problem. How are the three tables related?

Also, I wouldn't call it smoke and mirrors per se. What you are doing in an SQL statement is decribing the results you want from the database, rather than how they are retrieved.

So if you have three tables in this kind of relationship:


t1 --> t2 --> t3

Where one record in t1 has many possible children in t2, and each record in t2 has many children in t3, you want a join like this: t1 INNER JOIN t2 ON t1.field1 = t2.field1 INNER JOIN t3 ON t2.field2 = t3.field2 (where field1, and field2 hold the same value) to get a result with the fields you selected from t1, t2 and t3 only where they match on the key field.

If you do a left (outer) join you get a record containing the fields from t1 for each record from t1, and the fields from t2 with NULL's for those fields where they didn't match. In a three way join, the same rules apply, except the result of the first join will be used to join the third table. So OUTER JOINS (like LEFT) are useful when you have a table where you want a result record for EVERY record in the table on the LEFT (in a LEFT JOIN) and get what data matches from the right side table.

You still need to have a valid join field, or you get a Cartesian product.

In short, I think the query might need rephrased. Can you post the entire query, and a description of what the result set you are looking for looks like?