Very Ugly SQL statement. Help Needed.

[jpresley]

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:

Code: Select all

<%@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)
%>

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

[jpresley]

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

[ukgeoff]

Whilst one could hopefully make an educated guess, I think it would be useful if you edited your original post and indicated primary, secondary and foreign keys.