Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2
  1. #1
    Regular Coder
    Join Date
    Mar 2005
    Posts
    111
    Thanks
    0
    Thanked 0 Times in 0 Posts

    mysql IF statements

    I looked through the mysql manual but their examples are pretty confusing
    anyone think they could show me the correct way to write an IF statement inside of a query like this one?

    Code:
    SELECT orders.order_id, students.fname, students.lname, order_status.order_status_name, course.name AS course_name, date_format(class.startdate, '%m/%d/%Y') AS startdate, date_format(class.enddate, '%m/%d/%Y') as enddate, order_type.order_type FROM orders LEFT JOIN students ON orders.student_id = students.student_id LEFT JOIN order_status ON orders.order_status = order_status.order_status_id LEFT JOIN class ON orders.class_id = class.class_id LEFT JOIN course ON orders.course_id = course.course_id LEFT JOIN order_type ON orders.order_type = order_type.type_id IF(order_type.type_id == 1) THEN SELECT lm_number FROM lmcharge WHERE orders.order_id = lmcharge.order_id; END IF WHERE orders.order_id = '8'

  • #2
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    You need to put your selections (including the IF statement) before your "from" clause.

    Code:
    SELECT orders.order_id
    ,students.fname
    , students.lname
    , order_status.order_status_name
    , course.name AS course_name
    , date_format(class.startdate, '%m/%d/%Y') AS startdate
    , date_format(class.enddate, '%m/%d/%Y') as enddate
    , order_type.order_type
    , IF(order_type.type_id == 1, lmcharge.lm_number, 0)
    FROM orders
    LEFT JOIN students ON orders.student_id = students.student_id
    LEFT JOIN order_status ON orders.order_status = order_status.order_status_id
    LEFT JOIN class ON orders.class_id = class.class_id
    LEFT JOIN course ON orders.course_id = course.course_id
    LEFT JOIN order_type ON orders.order_type = order_type.type_id
    LEFT JOIN lmcharge ON orders.order_id = lmcharge.order_id
    WHERE orders.order_id = '8'


  •  

    Posting Permissions

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