Help with theory behind specific table design and sql select statements...

Ok, this might be a tad long but will make sense. Im putting together a DB of products to be browsed and eventually end up at a list of vendors for a specific item.

Right now im concerned with 3 tables. One table is a list of possible categories that a product could fall under. (ex. phones, ksu's, accessories, power supplies) The table is 2 column, catID and category.

The next table is a Manufacturer table, defines all of the possible manufactures/brands a product could fall under. Again 2 columns, ManuID and manu

Third table is the table of products them selves... 6 columns, prodID, item, model, manuID, catID and photo (for later use)

At this point, I am trying to build a list of CATERGORIES for a particular Manufacturer. Bascially, whatever of the who knows how many categories that happen have parts that fit that manufacturer, display the categories (then, each would be linked so that clikcing the cat provides a list of parts under that cat, which is under the manufacturer.. )

At this point, ive realized that the only place I tie the part and the category together is in the products table.

How'm i gonna display this list of matching categories now.
I would obviuosly have to run down the list of products and pull out each catID that matches the ManuID already specified. But as a think about that, this list of parts will eventually be prolly 10000 or more records deep.

Anyway, im trying to get the gist of the theory behind all this...

would I

Select CatID from PRODUCTS where manuID = SpecifiedManuID

and then... i dunno.. am i thinkin about this screwy,, can anyone point me in the direction of how to tie this together.. I just need to print the list of categories that have parts and match the manufacturer requested.

To get the list of categories for a manufacturer, based on the products they sell, you could do something like this

does the a b and c represent anything.... or are they just holders (buffers)...

Im really NOT trying to be freakin dense here... but i guess im just so new at this.

lemme breakdown the no bulls of it.
ManuID was passed from pervious page, so now i KNOW what manufacturer they would like to see categories for..

3 tables.
-------------
ttProduct : 'to hold info for each individual item
columns = ProdID, item, model, manuID, catID, photo
-------------
ttCategories : 'to hold the multitude of possible categories parts could fit under
cloumns = catID, category
-------------
ttManu : 'to hold all possible manufacturer / brand combos
columns = manuID, manu
-------------

so
catRs.open
"SELECT A.category
from ttCategories
INNER JOIN ttProducts As B on (A.catID = B.CatID)
INNER JOIN ttManu As C On (B.manuID = C.manuID)
WHERE C.manuID = " & manuID
------------
?? Now what kind of recordset is that going to give me....

cause see, now what i need to do is display the list of categories that match the bill for requested manu set in links to pass the category on to the next page, where now that I know the manuID and the CatID can display a list of products that fit BOTH in order to display a list of vendors that carry said product arranged by price lol i dunno what the **** im doing...

Im thnking that once i do this, now i have the the category field available to me in the recordset right...


so i can build links based on the catID and the manuID and display the category in the middle.. right?

Ok obviuosly by now, ive figured out that a means something else....

Its the AS part that gets me....

but i got part of it figured out..

I used this statment..
catRs.open "SELECT ttCategory.category from ttCategory INNER JOIN ttProducts on ttCategory.catID = ttProducts.CatID WHERE ttProducts.manuID = '" & manuID & "'",objCn, 1, 3

and then in the page
---------------

Dim x
do until catRs.EOF
for each x in catRs.fields
response.write(x.name)
response.write(" = ")
response.write(x.value & "<br>")
next
response.Write("<br>")
catRs.movenext
loop
-------------------------
And it returns obviuosly multiples of the same, for each time it finds it, it returns a row...

category = Phones

category = Phones

category = Cards, Processors and Addons

category = Cabinets, KSU's & Power Supplies

category = Cabinets, KSU's & Power Supplies

category = Voicemail Units
---------------------------------
Ok so great, I got that much, now i need to figure out how to display eachonly once, and then hook it into a link to the next page while passing the manuID and the catID

Dood im pluggin away here..

I got to here...

catRs.open "Select ttCategory.category, ttCategory.catID from ttCategory
INNER JOIN ttProducts AS Category on (ttCategory.catID = ttProducts.CatID)
INNER JOIN ttManu AS Products On (ttProducts.manuID = ttManu.manuID)
where manuID ='" & manuID & "'",objCn, 1, 3

Im thinking this all has to do with the select statement

I should end up with a recordset that merges all three tables first cat table and prod table based on cat id and then that new set to the manu table based on manuid between the two right? and only where that manuID = the page level manuID

but it tells me

Microsoft OLE DB Provider for SQL Server error '80040e14'

The column prefix 'ttProducts' does not match with a table name or alias name used in the query.

and will this select statement get me the results i need?

A, B and C are just aliases for the table names, so you don't have to type (Manufacturer.ManuID) you just do (INNER JOIN Manufacturer As C), then you can just use C.fieldname, to represent the fieldname to pull from the manufacturers table.