SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Member
    Join Date
    Sep 2006
    Posts
    7
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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:
    Code:
    <%@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

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    hey jason

    how do you know that a particular store actually carries/stocks a particular product?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member
    Join Date
    Sep 2006
    Posts
    7
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    yes, that explains it

    say the user submits productID=937 and stateID=42...
    Code:
    select wtbStores.Alias as store_name
      from wtbProductWarehouses
    inner
      join wtbWarehouseStores
        on wtbWarehouseStores.warehouseID = wtbProductWarehouses.warehouseID
    inner
      join wtbStoreStates
        on wtbStoreStates.storeID = wtbWarehouseStores.storeID
       and wtbStoreStates.stateID = 42
    inner
      join wtbStores
        on wtbStores.storeID = wtbStoreStates.storeID   
     where wtbProductWarehouses.productID = 937
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Wizard siteguru's Avatar
    Join Date
    Oct 2002
    Location
    Scotland
    Posts
    3,629
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    If it's Access then you might need some parentheses in there - I have found that Access doesn't like INNER JOINS unless each segment is properly bounded.

    http://www.sitepoint.com/forums/showthread.php?t=422806
    Ian Anderson
    www.siteguru.co.uk

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    well spotted, ian, i guess i overlooked that bit about it being access
    Code:
    select wtbStores.Alias as store_name
      from ((
           wtbProductWarehouses
    inner
      join wtbWarehouseStores
        on wtbWarehouseStores.warehouseID = wtbProductWarehouses.warehouseID
           )
    inner
      join wtbStoreStates
        on wtbStoreStates.storeID = wtbWarehouseStores.storeID
       and wtbStoreStates.stateID = 42
           )
    inner
      join wtbStores
        on wtbStores.storeID = wtbStoreStates.storeID   
     where wtbProductWarehouses.productID = 937
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Zealot yacka's Avatar
    Join Date
    Aug 2006
    Posts
    124
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Is that a legitimate error message? If so it looks like the the stateid and productid values are not being supplied to the query. Your code is also vulnerable to sql injection.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •