There is a difference between 'analysis' and 'design', a little bit like it
is, for a native English speaking, a difference to translate FROM a foreign
language, and to translate TO the foreign language: the second is harder
because you have to care about some rules, in addition to have the right
word.
In SQL, the analysis is also easier. Starts with a CROSS JOIN.
SELECT a.*, b.*
FROM a, b
Then, if there are n records in a, and m records in b, the result will have
m*n records, ie, for each record in a, the result will 'merge'
(horizontally) each row of b.
So, if table d6, one field, dice, has 6 records, with values from 1 to 6,
then
SELECT a.dice, b.dice
FROM d6 AS a, d6 AS b
will result in 36 records, from {1, 1} to {6, 6}, or, if you prefer, the
result can be compared to an explicit list of possibilities to throw two
6-sides dices. ( I assume you know what aliases are, so I wont insist on
this concept).
Now, for an INNER JOIN, you start with a CROSS JOIN, but you keep only the
records, IN THE RESULT, where the ON clause evaluates to TRUE (remove
records where the ON clause evaluates to FALSE or NULL ). Note that this is
the intended result. The query plan can use ANY method to get it, NOT
NECESSARY going through a cross join first, like we do here.
As example, what is the probability to have the second dice higher than the
first one, when we throw two six sides dices?
SELECT a.dice, b.dice
FROM d6 AS a INNER JOIN d6 AS b
ON b.d6 > a.d6
will simply list these possibilities, for us. Let us start it, manually:
Cross join record ON clause
1 1 false don't
keep it
1 2 true keep
it
1 3 true keep
it
1 4 true keep
it
1 5 true keep
it
1 6 true keep
it
2 1 false don't
keep it
2 2 false don't
keep it
2 3 true keep
it
...
and so on. The result should return 15 rows. So, the probability we were
looking for is a little bit less than 42% (15 / 36 ).
Now, that is how you can ANALYZE the inner join: having one, you can
'manually' translate it. It is quite different to start from scratch and
get the SQL statement, that would be the DESIGN part. But before we get good
at design, in general, we should 'study' by 'analysis', and then, recuperate
building elements we have observed, here and there.
How can we ANALYZE the first query without using the cross join? Well, maybe
you can imagine that each table is a vertical list. Have your index from
your left hand over tableOne and your index from your right hand over
tableTwo. Somewhere, in the middle of the process, our left hand point some
record of tableOne. The right hand is NOT allowed to point to any record,
no. Through:
ON a.name = b.name AND a.workDate >= b.DateNewSal
we can see that the right hand, "b", can only 'stop' on records where the
relation holds So, in our case, "b" can scan all the tableTwo records which
speak about the same 'name', but with a dateDateStap in 'b', occurring
before the one actually pointed by our left hand... And from all the records
so reached by the right hand, we take the MAX( payRate ).
Sure, we CANNOT use that 'fast' analysis technique every where. Here, it
works because of the GROUP BY clause, which identifies, somehow, the place
our left hand has to stop, and we assume that these groups define ONE and
only ONE record in tableOne. If that would not have been true, we would not
have been able to use that 'resolution shortcut'.
The second query is more complex. First, I made a mistake about it, it
should be:
SELECT a.name, LAST(a.workHours), a.workDate, LAST(b.payRate)
FROM ( tableOne As a INNER JOIN tableTwo As b
ON a.name=b.name AND a.workDate >= b.dateNewSal)
INNER JOIN tableTwop AS c
ON a.name= c.name AND a.workDate >=c.dateNewSal
GROUP BY a.name, a.workDate, b.dateNewSal
HAVING b.dateNewSal=MAX(c.dateNewSal)
We don't have three hands, after all, but "borrow" a friend for some seconds
if you need an extra hand. Look again at the GROUP clause, and note that it
identifies ONE possible combination of {tableOne, tableTwo} records, but
here, it is the third 'hand' , c, that can move over some records (once your
first two hands are 'fixed', as given by the GROUP clause). Since the third
hand is NOT limited by the second hand, it can point to a dateNewSal that is
GREATER (occur after) the dateNewSal actually pointed by the second hand.
Those groups will be discarded (by virtue of the HAVING clause). So, only
when the second hand points to the maximum possible date (while respecting
a.workDate >= b.dateNewSal ), will we have a group kept. For such groups,
indeed, payRate is then the appropriate value, since NO more record exists
between b.dateNewSal and a.wordDate.
Again, the shortcut we used, for this analysis, are only applicable because
of the unique-ity we got from the GROUP and the ON clause between the tables
implied in the GROUP.
Vanderghast, Access MVP
[quoted text clipped - 29 lines]
