Why rename tables with P., X., etc. 2

[kmarr]

I am new to this. The query below is Golom's response to one of my earlier questions (It produced the desired result by the way) Thanks!

In the following SQL query, there is a P. and an M. preceeding the fields in the select command and later in the From command the table names are preceeded by the P. and M.

Why??

Also, on the Inner join the "M" that preceeds "Inner" is not understood by me.

Toward the bottom of the query, X. is used. - also not understood.

Please enlighten me.

Thanks in advance.


Select M.str_ProdDesc, M.Str_UOM, P.Str_VendorItemID,
P.Str_VendorID, P.Num_MarItemID,
P.Cur_VendorPrice, P.Date_QuoteDate

From Tbl_MarItems M Inner Join Tbl_VendorPrice P
ON M.Auto_MarItemID = P.Num_MarItemID


WHERE P.Cur_VendorPrice =

(Select MIN(X.Cur_VendorPrice) From Tbl_VendorPrice X
Where X.Num_MarItemID = M.Auto_MarItemID )

 

[jrbarnett]

Hi

The "P" and "M" are aliases to the table names.
There are a number of times when table aliases are used in a query:

1) to make it easier to read, and shorter to code.
2) To accomodate multiple instances of the same table, perhaps joining to different fields.

Example:
In the select line, it brings in data from the table "tbl_VendorPrice" called "P".
However, in the where clause, it uses a correlated subquery to join a second instance of the same table, called X to the table M.
You could not just use "P" there because it would only have the data available that it would have as part of the initial table joins between P and M.

Best advice I can give you is to look up correlated subqueries in the database books and websites, and try things out - see what happens if you change the X at the bottom to P or use the table names without the aliases. Correlated subqueries are difficult to get to grips with first of all, but once you have the hang of them they are very useful.

John

 

[dhookom]

Great explanation John! Deserves a star.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[jrbarnett]

Thanks Duane.

John

 

[kmarr]

Thanks John! That helps me a lot.

Ken