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**************@TK2MSFTNGP09.phx.gbl...
Oli wrote: Hi
In my company one of the people who works for me has done this:
sqlText = "SELECT products.ProdID, productName, " _
& "productPrice, quantity from products, " _
& "itemsOrdered where " _
& "products.productID = itemsOrdered.productID "_
& "and itemsOrdered.orderID = " & 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
