SELECT statement

[Oli]

Hi

In my company one of the people who works for me has done this:

sqlText = "SELECT products.ProdID , productName, " _
& "productPri ce, quantity from products, " _
& "itemsOrder ed where " _
& "products.produ ctID = itemsOrdered.pr oductID "_
& "and itemsOrdered.or derID = " & intOrderID

I am just a little unsure what products.ProdID means when later on in the
string there is the FROM products?

Anyone tell me?

THanks
Oli

[Jos]

Oli wrote:

Hi

In my company one of the people who works for me has done this:

sqlText = "SELECT products.ProdID , productName, " _
& "productPri ce, quantity from products, " _
& "itemsOrder ed where " _
& "products.produ ctID = itemsOrdered.pr oductID "_
& "and itemsOrdered.or derID = " & intOrderID

I am just a little unsure what products.ProdID means when later on in
the string there is the FROM products?

Anyone tell me?

THanks
Oli

This is just the most extensive way of writing the command.
In your particular case, you are allowed to drop all the "products."
prefixes.
They are only needed when you are querying more than one table
(just for the case where 2 tables would have a column with the
same name).

--

Jos

[Aaron Bertrand [MVP]]

Oli *is* selecting from two tables, and *should* prefix all column names
with table names to make things clear and unambiguous (both for the software
and for a developer viewing the code). I also suggest using an
ANSI-standard INNER JOIN syntax rather than this shorthand implicit join.
This way, you can separate the join clause(s) from the where clause(s),
making the relationship(s) easier to understand, and the code easier to
maintain. You can shorten things by using aliases for the table names, and
you can also keep the "short name" for each column, e.g.

SELECT
ProductId = p.ProductID,
productName = p.produectName,
productPrice = p.produectPrice ,
quantity = i.quantity
FROM
Products p
INNER JOIN
itemsOrdered i
ON p.productID = i.productID
WHERE
...

(Also, is your column named prodID or productID? And why do the productName
and productPrice columns need the word "product" in their name? Isn't that
implied, since they're already in the products table?)

--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Jos" <jo************ ***@fastmail.fm > wrote in message
news:OP******** ******@TK2MSFTN GP09.phx.gbl...

Oli wrote:

Hi

In my company one of the people who works for me has done this:

sqlText = "SELECT products.ProdID , productName, " _
& "productPri ce, quantity from products, " _
& "itemsOrder ed where " _
& "products.produ ctID = itemsOrdered.pr oductID "_
& "and itemsOrdered.or derID = " & intOrderID

I am just a little unsure what products.ProdID means when later on in
the string there is the FROM products?

Anyone tell me?

THanks
Oli

This is just the most extensive way of writing the command.
In your particular case, you are allowed to drop all the "products."
prefixes.
They are only needed when you are querying more than one table
(just for the case where 2 tables would have a column with the
same name).

--

Jos

[TJS]

products.ProdID is reference to the field name ProdID in the products table
products is the table name
"Oli" <ol*@NOSPAMoliw oods.co.uk> wrote in message
news:c3******** **@sparta.btint ernet.com...

Hi

In my company one of the people who works for me has done this:

sqlText = "SELECT products.ProdID , productName, " _
& "productPri ce, quantity from products, " _
& "itemsOrder ed where " _
& "products.produ ctID = itemsOrdered.pr oductID "_
& "and itemsOrdered.or derID = " & intOrderID

I am just a little unsure what products.ProdID means when later on in the
string there is the FROM products?

Anyone tell me?

THanks
Oli

[Tom B]

Isn't Name a reserved word? Thus productName

"Aaron Bertrand [MVP]" <aa***@TRASHasp faq.com> wrote in message
news:Oo******** ******@TK2MSFTN GP12.phx.gbl...

Oli *is* selecting from two tables, and *should* prefix all column names
with table names to make things clear and unambiguous (both for the software and for a developer viewing the code). I also suggest using an
ANSI-standard INNER JOIN syntax rather than this shorthand implicit join.
This way, you can separate the join clause(s) from the where clause(s),
making the relationship(s) easier to understand, and the code easier to
maintain. You can shorten things by using aliases for the table names, and you can also keep the "short name" for each column, e.g.

SELECT
ProductId = p.ProductID,
productName = p.produectName,
productPrice = p.produectPrice ,
quantity = i.quantity
FROM
Products p
INNER JOIN
itemsOrdered i
ON p.productID = i.productID
WHERE
...

(Also, is your column named prodID or productID? And why do the productName and productPrice columns need the word "product" in their name? Isn't that implied, since they're already in the products table?)

--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Jos" <jo************ ***@fastmail.fm > wrote in message
news:OP******** ******@TK2MSFTN GP09.phx.gbl...

Oli wrote:

Hi

In my company one of the people who works for me has done this:

sqlText = "SELECT products.ProdID , productName, " _
& "productPri ce, quantity from products, " _
& "itemsOrder ed where " _
& "products.produ ctID = itemsOrdered.pr oductID "_
& "and itemsOrdered.or derID = " & intOrderID

I am just a little unsure what products.ProdID means when later on in
the string there is the FROM products?

Anyone tell me?

THanks
Oli

This is just the most extensive way of writing the command.
In your particular case, you are allowed to drop all the "products."
prefixes.
They are only needed when you are querying more than one table
(just for the case where 2 tables would have a column with the
same name).

--

Jos

[Aaron Bertrand [MVP]]

> Isn't Name a reserved word? Thus productName

It should be avoided, but it's legal syntax and will work as it is...

--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/

[Oli]

Ahhh, I think I get you now!
THanks
"Oli" <ol*@NOSPAMoliw oods.co.uk> wrote in message
news:c3******** **@sparta.btint ernet.com...

Hi

In my company one of the people who works for me has done this:

sqlText = "SELECT products.ProdID , productName, " _
& "productPri ce, quantity from products, " _
& "itemsOrder ed where " _
& "products.produ ctID = itemsOrdered.pr oductID "_
& "and itemsOrdered.or derID = " & intOrderID

I am just a little unsure what products.ProdID means when later on in the
string there is the FROM products?

Anyone tell me?

THanks
Oli