SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Addict AshleyH's Avatar
    Join Date
    Mar 2005
    Posts
    260
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    An error on the server...works fine locally??

    I have this SQL:

    Code:
    SELECT tbl_customers.cst_FirstName
    , tbl_customers.cst_LastName
    , tbl_customers.cst_Zip
    , tbl_customers.cst_ID
    , tbl_orders.order_ID
    , tbl_orders.order_Date
    , tbl_orderskus.orderSKU_orderID
    , tbl_orderskus.orderSKU_SKU
    , tbl_skus.sku_id
    , tbl_skus.sku_MerchSKUID 
    
    FROM tbl_skus, tbl_customers 
    
    INNER JOIN 
    tbl_orders ON tbl_customers.cst_ID = tbl_orders.order_CustomerID 
    AND tbl_orders.order_ID = tbl_orderskus.orderSKU_orderID
    
    INNER JOIN 
    tbl_orderskus ON tbl_skus.sku_id = tbl_orderskus.orderSKU_SKU
    
    WHERE tbl_orders.order_Date >= #CreateODBCDate(LSParseDateTime(FORM.StartDate))#
    
    AND tbl_orders.order_Date <= #CreateODBCDateTime(DateAdd("d",1,LSParseDateTime(FORM.EndDate)))#
    
    <cfif FORM.Status NEQ 0>AND order_Status = #FORM.Status#</cfif>
    ORDER BY tbl_orders.order_Date DESC
    Works perfectly on my machine but throws up an error on the server using exactly the same database...can anyone shed any light on it!?


    [Macromedia][SequeLink JDBC Driver][ODBC Socket][MySQL][ODBC 3.51 Driver][mysqld-5.0.41-community-nt-log]Unknown column 'tbl_orderskus.orderSKU_orderID' in 'on clause'

    The error occurred in C:\FTP\H01Sttma44er\thewoolcompany\cw2\Admin\Orders.cfm: line 71
    69 : AND tbl_orders.order_Date <= #CreateODBCDateTime(DateAdd("d",1,LSParseDateTime(FORM.EndDate)))#
    70 :
    71 : <cfif FORM.Status NEQ 0>AND order_Status = #FORM.Status#</cfif>
    72 : ORDER BY tbl_orders.order_Date DESC
    73 : </cfquery>

    Many thanks Ashley

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,275
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    we've seen this error a lot

    your local is on 4.x, your server is on 5.x, which is more strict about invalid SQL

    no reflection on you, but your joins are a wee bit sloppy

    see the part in blue here --
    Code:
    FROM tbl_skus, tbl_customers 
    INNER JOIN 
    tbl_orders ON tbl_customers.cst_ID = tbl_orders.order_CustomerID 
    AND tbl_orders.order_ID = tbl_orderskus.orderSKU_orderID
    INNER JOIN 
    tbl_orderskus ON tbl_skus.sku_id = tbl_orderskus.orderSKU_SKU
    the part in blue is evaluated first, and within its scope, the tbl_orderskus table hasn't been encountered yet

    you're mixing comma-list syntax with JOIN syntax, never a good idea

    try it like this --
    Code:
      FROM tbl_orders
    INNER 
      JOIN tbl_customers
        ON tbl_customers.cst_ID = tbl_orders.order_CustomerID 
    INNER
      JOIN tbl_orderskus
        ON tbl_orderskus.orderSKU_orderID = tbl_orders.order_ID
    INNER 
      JOIN tbl_skus
        ON tbl_skus.sku_id = tbl_orderskus.orderSKU_SKU
    notice how much more orderly that looks, and how simple it is, joining each table onto a previously mentioned one

    notice also that it starts with the table that has most specificity (as provided by the WHERE clause)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Addict AshleyH's Avatar
    Join Date
    Mar 2005
    Posts
    260
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi Rudy,

    Thanks for that! It worked perfectly.

    Many thanks
    Ashley


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
  •