I am trying to list all products, but instead of listing the Category ID (which is in the product table), I want to list the Category NAME (which is in the category table).
Here is my current SQL statement which is giving me a syntax error. Hopefully, you can see what I’m trying to do:
strSQL = "SELECT * FROM Products INNER JOIN ON products.CatID = Categories.ProdID "
I figure I just need to join the tables. Then I can pull fields from either table, right? ie:
strSQL = "SELECT * FROM Products INNER JOIN ON products.CatID = Categories.ProdID "
Where am I going wrong?
[/QUOTE]
When I write SQL I remember that it’s an easy to read language. Your query says the following:
Show me everything from both the products and … (there should be a table name here) what table where the product category id matches the category product id.
What you need it to say is:
Show me the fields that I need from the products and category tables where the product category id and the category category id match.
That is written in SQL as:
SELECT Products.Name as ProdName, Categories.name as CatName
FROM products INNER JOIN categories ON
Products.CatID = categories.CatID
You will need to replace the select list with the fields you want. It is very inefficent to do SELECT * when you’re only showing 3 fields.