Understanding Query SQL

[Sprinks]

A while back I asked for help with a query from an Estimates database, where
I wanted to return only the last Estimate record. The very helpful
respondant supplied the following SQL, which works perfectly. Unfortunately,
I did not fully understand how it worked at the time, and I find myself in a
similar situation.

I follow the joins and the selections, but don't understand the
criteria--mostly, why these dummy tables M and T are used. Can anyone teach
me how to fish and explain it?

SELECT M.JobNumber, M.EstimateNumber, tblEstimateTypeList.EstimateType,
M.Amount, M.SForQty, M.Unit, M.Note
FROM tblBids INNER JOIN (tblEstimates AS M INNER JOIN tblEstimateTypeList ON
M.EstimateType=tblEstimateTypeList.EstimateTypeID) ON
tblBids.JobNumber=M.JobNumber
WHERE (((M.EstimateNumber)=(SELECT Max(EstimateNumber) FROM tblEstimates
As T WHERE T.JobNumber = M.JobNumber)) AND ((tblBids.AwardedBid)=True));

Thanks.

Sprinks

 

[Dan Artuso]

Hi,
You'll notice that the criteria is itself a query which pulls data from the same table
(tblEstimates) as in the main query. It's a lot clearer if we create an 'alias' for the table so we know
*which* table is being referenced in each of the Select statements, thus the use of:
tblEstimates AS M
tblEstimates As T

Now when we do this, we have to use the alias in the Select statement as well, so...
SELECT M.JobNumber, M.EstimateNumber etc

 

[Brian]

A while back I asked for help with a query from an Estimates database, where
I wanted to return only the last Estimate record. The very helpful
respondant supplied the following SQL, which works perfectly. Unfortunately,
I did not fully understand how it worked at the time, and I find myself in a
similar situation.

I follow the joins and the selections, but don't understand the
criteria--mostly, why these dummy tables M and T are used. Can anyone teach
me how to fish and explain it?

SELECT M.JobNumber, M.EstimateNumber, tblEstimateTypeList.EstimateType,
M.Amount, M.SForQty, M.Unit, M.Note
FROM tblBids INNER JOIN (tblEstimates AS M INNER JOIN tblEstimateTypeList ON
M.EstimateType=tblEstimateTypeList.EstimateTypeID) ON
tblBids.JobNumber=M.JobNumber
WHERE (((M.EstimateNumber)=(SELECT Max(EstimateNumber) FROM tblEstimates
As T WHERE T.JobNumber = M.JobNumber)) AND ((tblBids.AwardedBid)=True));

Thanks.

Sprinks

M and T are not dummy tables, they are Aliases. M is simply an alias
(another name) that you choose to use for tblEstimates() ("tblEstimates AS
M"). There are two reasons for doing this, one is that "M" is a lot quicker
to type than "tblEstimates", and the other is that it allows you to include
the same table twice in the FROM clause, should you need to, by giving it
two different aliases.

 

[Sprinks]

Brian and Dan,

Thanks for your help. I get it now.

Sprinks