Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2003
    Posts
    6

    Question Unanswered: What's wrong with this statement??

    Can someone please show me how to re-write this statement for MySQL? I wrote it in Access using the wysiwyg and then copied the SQL. I get a very generic error when I run it, however and cannot figure out what is wrong. I can usually write my own statements, but when I get too many JOINS, I get confused.

    I'd appreciate any help.

    SELECT orders.orderID, orders.*, products.ccode, products.cname, products.cprice, oitems.numitems, customers.* FROM (customers INNER JOIN orders ON customers.custID = orders.ocustomerid) INNER JOIN (products INNER JOIN oitems ON products.catalogID = oitems.catalogid) ON orders.orderID = oitems.orderid WHERE (((orders.orderID)=2))

  2. #2
    Join Date
    Sep 2002
    Location
    Montreal, Canada
    Posts
    224
    Provided Answers: 1

    Re: What's wrong with this statement??

    Originally posted by big_poppa
    Can someone please show me how to re-write this statement for MySQL? I wrote it in Access using the wysiwyg and then copied the SQL. I get a very generic error when I run it, however and cannot figure out what is wrong. I can usually write my own statements, but when I get too many JOINS, I get confused.

    I'd appreciate any help.

    SELECT orders.orderID, orders.*, products.ccode, products.cname, products.cprice, oitems.numitems, customers.* FROM (customers INNER JOIN orders ON customers.custID = orders.ocustomerid) INNER JOIN (products INNER JOIN oitems ON products.catalogID = oitems.catalogid) ON orders.orderID = oitems.orderid WHERE (((orders.orderID)=2))
    FROM tablename INNER...

    NOT

    FROM (tablename INNER ...

  3. #3
    Join Date
    Oct 2003
    Posts
    6

    Re: What's wrong with this statement??

    I removed the first set of parenthesis:

    SELECT orders.orderID, orders.*, products.ccode, products.cname, products.cprice, oitems.numitems, customers.* FROM customers INNER JOIN orders ON customers.custID = orders.ocustomerid INNER JOIN (products INNER JOIN oitems ON products.catalogID = oitems.catalogid) ON orders.orderID = oitems.orderid WHERE (((orders.orderID)=2))

    But I still get this vague message:

    You have an error in your SQL syntax near '(products INNER JOIN oitems ON products.catalogID = oitems.catalogid) ON orders.'

    which is where the error was ocurring before as well

  4. #4
    Join Date
    Sep 2002
    Location
    Montreal, Canada
    Posts
    224
    Provided Answers: 1

    Re: What's wrong with this statement??

    Originally posted by big_poppa
    I removed the first set of parenthesis:

    SELECT orders.orderID, orders.*, products.ccode, products.cname, products.cprice, oitems.numitems, customers.* FROM customers INNER JOIN orders ON customers.custID = orders.ocustomerid INNER JOIN (products INNER JOIN oitems ON products.catalogID = oitems.catalogid) ON orders.orderID = oitems.orderid WHERE (((orders.orderID)=2))

    But I still get this vague message:

    You have an error in your SQL syntax near '(products INNER JOIN oitems ON products.catalogID = oitems.catalogid) ON orders.'

    which is where the error was ocurring before as well
    INNER JOIN (products
    same thing..
    should be
    INNER JOIN products

  5. #5
    Join Date
    Oct 2003
    Posts
    6

    Re: What's wrong with this statement??

    yeah, i had tried that after your last suggestion. I tried a bunch of different groupings hoping to hit on something, but no luck

    You have an error in your SQL syntax near 'INNER JOIN oitems ON products.catalogID = oitems.catalogid ON orders.orderID = o'

  6. #6
    Join Date
    Sep 2002
    Location
    Montreal, Canada
    Posts
    224
    Provided Answers: 1

    Re: What's wrong with this statement??

    Originally posted by big_poppa
    yeah, i had tried that after your last suggestion. I tried a bunch of different groupings hoping to hit on something, but no luck

    You have an error in your SQL syntax near 'INNER JOIN oitems ON products.catalogID = oitems.catalogid ON orders.orderID = o'
    I'm not sure about what you want to do but could you format your query like this... Would make it clearer to you and everyone else...


    SELECT orders.orderID, orders.*, products.ccode, products.cname, products.cprice, oitems.numitems, customers.*

    FROM customers

    INNER JOIN orders ON customers.custID = orders.ocustomerid
    INNER JOIN products ON ??????????????????????
    INNER JOIN oitems ON (products.catalogID = oitems.catalogid and orders.orderID = orderid )

    WHERE orders.orderID=2


    Looks like someth

  7. #7
    Join Date
    Oct 2003
    Posts
    6

    Re: What's wrong with this statement??

    you, my friend, are a genius!

    I think this worked (at least I didn't receive any errors). I now just need to verify that I'm getting the correct values I was looking for. Here's what I am using now:

    SELECT orders.orderID, orders.*, products.ccode, products.cname, products.cprice, oitems.numitems, customers.*
    FROM customers
    INNER JOIN orders ON customers.custID = orders.ocustomerid
    INNER JOIN products ON oitems.catalogid = products.catalogID
    INNER JOIN oitems ON (products.catalogID = oitems.catalogid and orders.orderID = oitems.orderid )
    WHERE orders.orderID=2

    Thank you so much for looking at this for me. Just looking at this last way that you structured it has actually given me a better understanding of what this query is really doing. Thanks again!

Posting Permissions

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