[AccessD] Subquery Theory Part 1

Keith had a thread called 'Select within query'.  I think we have his
problem solved, but I wanted to add a little more explanation to the
solution.

 

The problem was that Keith needed data from two tables that didn't have a
direct link.  In his particular case, he wanted a Payrate field from one
table, to match hours worked in another.  Both tables could be linked by an
employeenumber/ID field, however, it was a many to many join, which didn't
produce what he needed.  He needed to display the Payrate field from the
record with the right date.  The hours worked had a datestamp, however, the
table with the payrate had a datefield that would be older then the hours
worked table.  (ie, if someone got a raise on 12/1/2006, the records in the
hours worked table would need the previous payrate if they were worked
before 12-1-2006, and the new payrate if after 12-1-2006).

 

So I presented two solutions.  The first was to show how to just return ONE
field, using two subqueries (one within another).  The second was to show
how to return multiple fields, using just one subquery.  That is what this
email is about.

 

An INNER JOIN is a criteria the database engine (in this case Jet) uses to
return the appropriate records.  For example:

 

Table1

ID      Data

1                     Blue

2                     Red

3                     Green

4                     Yellow

 

Table 2

ID         Data

1                     Circle

2                     Square

3                     Triangle

4                     Line

 

In the two tables above, if we want to match the data fields by their ID
fields, we would use an INNER JOIN (which says show me all the records that
match on a particular field):

 

SELECT Table1.Data, Table2.Data

FROM Table1 INNER JOIN Table2 ON Table1.ID=Table2.ID

 

As I have stated, the inner join is a criteria, so it can also be placed in
the WHERE statement of a query, ie:

 

SELECT Table1.Data, Table2.Data

FROM Table1, Table2

WHERE Table1.ID=Table2.ID

 

Now, this is probably bad practice.  I'm not positive, but I would assume
that the INNER JOIN statement would run faster then the criteria in the
WHERE statement, at least in JET (some dbs don't allow for joins, the
version of Oracle we have here at work is such a creature...yeck....).  

 

But how the join works is important to know, when it comes to a subquery.
In Keith's situation, if we 'join' the tables using the subquery in the
WHERE clause of the SQL statement, we can add whatever fields we want!

 

What we did in Keith's example, is used a query that returned the Max value
of a date field with a WHERE clause that said the date field had to be less
then or equal to a selected date.  But since this is a 'lesson' post, let's
create a new data/query situation.  

 

(to be continued)