Hi,

Why this SQL below returns me this error:
---------
Syntax error (missing operator) in query expression 'productid=product_id 
(INNER JOIN subcat ON subcatcode=links.subcatcode_id)'. 
---------

Here's the SQL:
-----------
sSQL = "SELECT count(Nr) FROM products INNER JOIN links ON 
productid=product_id (INNER JOIN subcat ON subcatcode=links.subcatcode_id) 
where catcode =" & category(0,i) & ""
-----------

And here's the database structure:
------------
Categories
  catcode --     Catname
   1 --            Cloth
   2 --            Metal

Subcategories
  Subcatcode -- Catcode -- SubName
      1    --      1   --    Silk
      2    --      1   --     Cotton
      3    --      1   --     Wool
      4    --      2   --      Tin
      5    --      2   --     Aluminum

Records
   productid --   Nr       
      1   --     Pants      
      2   --     Shirts
      3   --      Cans

Links
   ID          Subcatcode_id --      product_id
    1               2   --              1     ' pants made out of cotton
    2               3   --              1     ' pants made out of wool
    3               1   --              2     ' shirts made out of silk
    4               2   --              2     ' shirts made out of cotton
    5               4   --              3     ' tin cans
    6               5   --              3     ' aluminum cans
-----------

What I want to do is to find out the total number of records from a 
category.
As you see there a record can be in more than one sub-category (I'm using 
Access).

Thanks,
Mircea 

Nevermind.

I found out why.
I changed into this and it's working:
---------
sSQL="SELECT count(products.productid) as productid FROM subcat INNER JOIN 
(products INNER JOIN links ON products.productid = links.product_id) ON 
subcat.subcatcode = links.subcatcode_id where catcode =" & category(0,i) 
& ""


Set RScount = db.Execute(sSQL)
rcount = RScount("productid")
Response.Write ("(" & rcount & ")")
----------

Mircea

> Hi,

> Why this SQL below returns me this error:
-> --------
S> yntax error (missing operator) in query 
expression 'productid=product_id 
(> INNER JOIN subcat ON subcatcode=links.subcatcode_id)'. 
-> --------

> Here's the SQL:
-> ----------
s> SQL = "SELECT count(Nr) FROM products INNER JOIN links ON 
p> roductid=product_id (INNER JOIN subcat ON 
subcatcode=links.subcatcode_id) 
w> here catcode =" & category(0,i) & ""
-> ----------

> And here's the database structure:
-> -----------
C> ategories
 >  catcode --     Catname
 >   1 --            Cloth
 >   2 --            Metal

> Subcategories
 >  Subcatcode -- Catcode -- SubName
 >      1    --      1   --    Silk
 >      2    --      1   --     Cotton
 >      3    --      1   --     Wool
 >      4    --      2   --      Tin
 >      5    --      2   --     Aluminum

> Records
 >   productid --   Nr       
 >      1   --     Pants      
 >      2   --     Shirts
 >      3   --      Cans

> Links
 >   ID          Subcatcode_id --      product_id
 >    1               2   --              1     ' pants made out of cotton
 >    2               3   --              1     ' pants made out of wool
 >    3               1   --              2     ' shirts made out of silk
 >    4               2   --              2     ' shirts made out of cotton
 >    5               4   --              3     ' tin cans
 >    6               5   --              3     ' aluminum cans
-> ----------

> What I want to do is to find out the total number of records from a 
c> ategory.
A> s you see there a record can be in more than one sub-category (I'm 
using 
A> ccess).

> Thanks,
M> ircea