0

we have three tables

1. customer 
2. custumerorder 
3. order_type

customer: custumer_id , custumer_name, custumer_address,custumer_phone,status

Order_type order_type_id,orderType,

custumerorder custumerorder_id,custumer_id,order_type_id ,order_date,Total_order,shipping_info

i want to trace out the last order details so i have query 

SELECT c.*,
       tblcusorder.order_dt,
       tblcusorder.orderType,
       tblcusorder.shipping_info
FROM customer c
LEFT JOIN
  ( SELECT max(cr.order_date) AS order_dt,
           ot.orderType,
           cr.custumer_id,
           cr.custumerorder_id,
           cr.order_type_id,
           cr.shipping_info
   FROM custumerorder cr
   INNER JOIN order_type ot ON ot.order_type_id=cr.order_type_id
   GROUP BY cr.custumer_id ) AS tblcusorder ON tblcusorder.custumer_id=c.custumer_id
WHERE c.status='TRUE'

Issue

as I am using Max() function on order date so its showing last order date which is ok, but rest of row data is not from that row (customer order) and ordertype is not link with that date .

user2314737
  • 27,088
  • 20
  • 102
  • 114
Navid
  • 21
  • 3
  • Have you tried anything??? If tried then shows us.. – Sagar Gangwal Aug 30 '17 at 06:58
  • I'm guessing what you meant to do was to *order* by cr.order_date desc and then grab the first. This will grab only one row of course, but you have to decide the information you want to view. You cannot mix and match queries with good results. Either you want the latest order or you want all orders, but not both. – Neil Aug 30 '17 at 07:01

0 Answers0