SQL statement help anyone?

[LegalAlien]

A newbie to SQL - in desperate need of some help!

I have two tables: Categories and Products

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:

<%=rs("catName")%>

Where am I going wrong?

 

[hatton]

strSQL = "SELECT * FROM Products INNER JOIN ON products.CatID = Categories.ProdID "

Where am I going wrong?

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.

Hatton

 

[LegalAlien]

Thanks Hatton! I "fixed it" :]