Michael,

First off I just wanted to say that I agree with Oleg's comments, so I will focus on this part of your post:

>Can a one-[one|many|none]-[one|many|none] relation be established between three tables in a single select without returning 'extra' records?

Sure, you want to do something like this:

t1

field1 pk
field2
field3

t2

field4 pk
field1 fk
field5

t3
field6 pk
field4 fk
field7

(where matching field names are primary keys, and foriegn keys and hold the same types as their namesakes in other tables)

Its assumed that these three tables are holding information about one entity (for example t1 is salesreps, t2 holds their clients, and t3 sales to that client)

So you could simply have a table like this:

t4
field1 pk
field2 (Name)
field3 (age)
field5 (client)
field7 (date of sale)

Now you have all the information about each rep, and it is in one recordset--however it is not normalized, so you end up with all of the limitations that go with storing data denormalized (see the million threads last month or so for every nuance of normalization). But, when you do an SQL select from t1, t2, and t3 it is t4 that you are rebuilding (and possibly only gathering a subset of).

So from t4, to get a subset that shows all reps info with time after a certain date (assuming field7 is a date field and STRICTDATE is 0)

SELECT field2, field3, field5, field7 FROM t4 WHERE field5 > {12/01/1999}

To get the same result from our normalized db we have to join it together:

SELECT t1.field2, t1.field3, t2.field5, t3.field7 ;
FROM t1 LEFT JOIN t2 ON t1.field1 = t2.field1 ;
LEFT JOIN t3 ON t2.field4 = t3.field4 ;
WHERE t3.field7 > {12/21/1998}

This will give us a record for each match (it will only show those from t1 who actually have child records that meet the criteria). So we end up with possibly more records than exist in t1 because it may have more than one child in t2 which in turn may have more than one child in t3 (remember the goal in this is to recreate the denormalized table. What it won't do is show those from t1 who have no field7 not meeting the criteria (including none at all).

To recreate t4 in its entirety, simply leave off the where clause (there will be nulls for unmatched fields).

To get exactly 1 record for record in table one, and not miss any data is not possible, because in the denormalized table you have multiple entries for field2, and field3 for multiple values in field5 and/or field7.

An inner join in the example above would result in a recordset containing only those items from t4 (the denormalized table) that had values in ALL fields. Since our where clause was for the childmost table, those results should be the same here.

If the WHERE clause used fields from t1 or t2 A LEFT JOIN may show nulls for some t2 and t3 fields, and an INNER JOIN would still only return records where all fields have values in the corresponding record in t4.

I don't know if this helps, or just makes it more confusing, but this is how I see the use of joins used--You use the joins to recreate the denormalized table your data represents, then use the the WHERE clause to limit the criteria of the records you retrieve. When you JOIN on multiple fields it is because you need both items to uniquely identify a record (ie you have a complex key).

(BTW errata, and mistake pointers, and disagreements are welcome, this is more or less of the top of my head, and I don't want to mislead anyone if its not right, but I DID test this somewhat in a sample DB)

HTH,
Bill


PS surround code in tages to keep formatting