Very Ugly SQL statement. Help Needed.

I'll try to make this as brief as possible. I am having trouble getting this SQL statement to work. Any suggestions would be appriciated. I am using Classic ASP with an Access Backend.

The object:
The user will submit a productid and stateid through a form to find a list of stores in the given state that sells the given product.

The DB schema:
wtbProducts (table)
productID
Alias

wtbProductWarehouses (table)
productID
warehouseID

wtbWarehouseStores (table)
warehouseID
storeID

wtbStoreStates (table)
storeID
stateID

wtbStores (table)
storeID
Alias (name of store)
WebAddress

Other tables which I don't think are needed in the SQL
wtbWarehouses (table)
warehouseID
Alias
CustomerNumber

wtbStates (table)
stateID
Code
Alias
Show

The data I want returned:
Alias and WebAddress from wtbStores

The code that I have written:

<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<!--#include  virtual="/Connections/Conn2.asp" -->
<%
    Dim conn, rs
    Set conn = Server.CreateObject("ADODB.Connection")
    Set rs = Server.CreateObject("ADODB.Recordset")
    conn.Open strConnect
   
    pid = Request.Form("productid")
    sid = Request.Form("stateid")
   
    SQL = " SELECT * FROM wtbStores " _
        & " WHERE storeID IN ( SELECT * " _
        & "                    FROM wtbStoreStates " _
        & "                    WHERE stateID = " & sid & ") " _
        & " AND storeID IN   ( SELECT * FROM wtbWarehouseStores " _
        & "                    WHERE warehouseID IN ( SELECT * " _
        & "                                              FROM wtbProductWarehouses " _
        & "                                              WHERE productID = " & pid & ")) "
       
    Set rs = conn.Execute(SQL)
%>

Code (markup):

The Error that I am getting:
Microsoft JET Database Engine error '80040e14'

Syntax error. in query expression 'storeID IN ( SELECT * FROM wtbStoreStates WHERE stateID = ) AND storeID IN ( SELECT * FROM wtbWarehouseStores WHERE warehouseID IN ( SELECT * FROM wtbProductWarehouses WHERE productID = ))'.

/getStores.asp, line 21

Any suggestions?

Thanks,

Jason

 

Sorry. I forgot to explain the product/warehouse/store relationship...

Warehouses order different products. This relationship is set in wtbProductWarehouses. Stores then can order any of the products that a Warehouse carries. If a Warehouse does not carry a product the store can not order it. The relationship of stores to Warehouses is set in wtbWarehouseStores. Thus in my thinking we have to use the productid submitted from the form to find warehouses which carry that product. Then we find which stores subscribe to those warehouses and then finally figure out which stores are in the stateid provided from the form.

I hope this explains the mess.

Thanks

 

try adding a response.write line just before your " Set rs = conn.Execute(SQL)" line to display your SQL statement to the browser. this should give you an idea as to what's going on. Your logic looks pretty sound to me on your sql statement, however if it were me, i'd think about using joins. Join statements, to me, are FAR less complicated than where... in statements.

for example, try something like:

sql = "select * from wtbStores inner join"
sql = sql & " (wtbStoreStates inner join"
sql = sql & " (wtbWarehouseStores inner join wtbproductWarehouses"
sql = sql & " on wtbWarehouseStores.WarehouseID = wtbproductWarehouses.WarehouseID)"
sql = sql & " on wtbStoreStates.StoreID = wtbWarehouseStores.StoreID)"
sql = sql & " on wtbStores.StoreID = wtbStoreStates.StoreID"
sql = sql & " where wtbStoreStates.stateID = " & sID
sql = sql & " and wtbProductWarehouses.productID = " & pID

should do pretty much what you're trying to accomplish here, only in a bit more orderly fashion with one query instead of several embedded queries.

let me know how it turns out.

EDIT:
i edited my query to make it a little bit prettier.

VG

 

I think ur Request can't be done.
I mean
pid = Request.Form("productid")
sid = Request.Form("stateid")

are empty.

Your current page can't get requested value of PID,SID.It shows Blank."stateID= & productID =".

So ur post data can't be Requested on this page.

So that u r getting this ERROR.

Just check that the variables value u r requesting is typed proper OR not.

Hope u can get Solution.