Results 1 to 11 of 11
  1. #1
    Join Date
    Apr 2009
    Posts
    2

    Unanswered: Querying 3 tables at once..!

    Hi,I have three tables

    order
    Order_ID
    OrderDate
    Dispatched (either 0 or 1)
    ....

    orderitem
    OrderItem_ID
    Order_ID
    Qty
    Product_ID

    product
    Product_ID
    Name
    Description
    Price
    ...

    I want to get a list of all the products within each order given a particular Order_ID.

    So say the Order_ID is 35, I want to return for each order item within it the qty, product name,description and price.

    At the moment I've tried

    Code:
    SELECT
       orderitem.Qty,
       product.Name AS ProductName,
       product.Description AS ProductDescription,
       product.Price
    FROM
       order INNER JOIN orderitem
           ON order.Order_ID = orderitem.Order_ID
       INNER JOIN product
           ON orderitem.Product_ID = product.Product_ID
    WHERE
       order.Order_ID = 35;
    But I'm just getting an error:
    "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'order INNER JOIN orderitem ON order.Order_ID = orderitem.Order_ID IN' at line 7"

    Find this join stuff really hard, any ideas what I'm doing wrong here?

    Thanks

  2. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    order is a reserved word, so you need to quote youre table name:
    Code:
    FROM
       "order" INNER JOIN orderitem
           ON "order".Order_ID = orderitem.Order_ID
       INNER JOIN product
           ON orderitem.Product_ID = product.Product_ID
    WHERE
       "order".Order_ID = 35;

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by thunderstorm654
    Find this join stuff really hard, any ideas what I'm doing wrong here?
    you understand joins quite well, your query is fine

    the problem is that ORDER is a reserved word

    change the name of your table

    many database designers prefer to use plural table names, since the idea is that a table is not a thing, but rather, a set of rows

    so orders would work nicely

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    shammat, quoting with doublequotes will work in mysql only if the appropriate server setting is switched on

    mysql likes to use the horrid backtick character -- `order` -- but whether doublequotes or backticks are used, you have to remember to do it everywhere, all the time

    renaming the table is a more suitable solution

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by r937
    shammat, quoting with doublequotes will work in mysql only if the appropriate server setting is switched on
    Are there really people out there using MySQL without the ANSI flag?

    Of course you are right, I always forget that I'm running MySQL in a extremely un-common way...

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    better to learn the lesson now on reserved words, and pay the pain of making the changes now rather than let a kludgy workaround remain in perpetuity
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Apr 2009
    Posts
    2
    thanks guys, turns out `order` worked;-), but think ill change it to order as that is indeed annoying and inconsistent- thanks

  8. #8
    Join Date
    Apr 2009
    Posts
    9
    Hi

    I think this is helpful for u

    SELECT
    product.name,
    product.description,
    product.price,
    orderitem.quantity
    FROM
    product JOIN orderitem
    ON product.product_id = orderitem.product_id
    JOIN orderitem
    ON orderitem.order_id = order.order_id
    WHERE
    Order.order_id = 35;

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by saurav.prasad28
    I think this is helpful for u
    it is not

    that query will produce a syntax error on order.order_id

    can you guess why?

    because your query does not include the order table

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Apr 2009
    Posts
    9

    Thumbs up ya their is mistake thanks for inform me but now it is right

    SELECT
    product.name,
    product.description,
    product.price,
    orderitem.quantity,order.order_id
    FROM
    product JOIN orderitem
    ON product.product_id = orderitem.product_id
    JOIN orderitem
    ON orderitem.order_id = order.order_id
    WHERE
    Order.order_id = 35;

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by saurav.prasad28
    ya their is mistake thanks for inform me but now it is right
    not, it is not right

    keep trying, you'll eventually get it

    by the way, you're not testing these queries, you're just writing them out of thin air, correct?

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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