SQL: How to eliminate orders whose status is CAN-CodePudding

With the Query below, I am unsuccessfully trying to get all orders that are not cancelled. Please point out the mistake in my SQL.

SELECT orders.id, orders.po_number, orders.placed_date, order_details.vendor_fk, vendor.vendor_name, orders.payment_method, orders.order_status, MAX(shipped_date) AS shipped_date FROM orders INNER JOIN order_details ON order_details.order_fk=orders.id INNER JOIN vendor ON vendor.id=order_details.vendor_fk WHERE ((orders.id=95837) AND (vendor.id=259)) OR ((orders.id=95838) AND (vendor.id=259)) OR ((orders.id=95841) AND (vendor.id=259)) OR ((orders.id=95842) AND (vendor.id=259)) OR ((orders.id=95845) AND (vendor.id=259)) OR ((orders.id=95846) AND (vendor.id=259)) OR ((orders.id=95848) AND (vendor.id=259)) OR ((orders.id=95851) AND (vendor.id=259)) OR ((orders.id=95853) AND (vendor.id=259)) OR ((orders.id=95859) AND (vendor.id=259)) OR ((orders.id=95860) AND (vendor.id=259)) OR ((orders.id=95862) AND (vendor.id=259)) OR ((orders.id=95863) AND (vendor.id=259)) OR ((orders.id=95864) AND (vendor.id=259)) OR ((orders.id=95865) AND (vendor.id=259)) OR ((orders.id=95866) AND (vendor.id=259)) OR ((orders.id=95868) AND (vendor.id=259)) OR ((orders.id=95880) AND (vendor.id=259)) OR ((orders.id=95910) AND (vendor.id=259)) OR ((orders.id=95948) AND (vendor.id=259)) OR ((orders.id=95956) AND (vendor.id=259)) OR ((orders.id=95958) AND (vendor.id=259)) OR ((orders.id=95962) AND (vendor.id=193)) OR ((orders.id=96077) AND (vendor.id=259)) OR ((orders.id=96080) AND (vendor.id=259)) OR ((orders.id=96101) AND (vendor.id=259)) OR ((orders.id=96204) AND (vendor.id=22)) OR ((orders.id=96225) AND (vendor.id=259)) OR ((orders.id=96226) AND (vendor.id=259)) OR ((orders.id=96230) AND (vendor.id=259)) OR ((orders.id=96237) AND (vendor.id=259)) OR ((orders.id=96244) AND (vendor.id=259)) OR ((orders.id=96255) AND (vendor.id=259)) OR ((orders.id=96258) AND (vendor.id=259)) OR ((orders.id=96263) AND (vendor.id=259)) OR ((orders.id=96264) AND (vendor.id=259)) OR ((orders.id=96266) AND (vendor.id=259)) OR ((orders.id=96268) AND (vendor.id=259)) OR ((orders.id=96271) AND (vendor.id=259)) OR ((orders.id=96273) AND (vendor.id=259)) OR ((orders.id=96274) AND (vendor.id=259)) OR ((orders.id=96275) AND (vendor.id=259)) OR ((orders.id=96276) AND (vendor.id=259)) OR ((orders.id=96278) AND (vendor.id=259)) OR ((orders.id=96279) AND (vendor.id=259)) OR ((orders.id=96280) AND (vendor.id=259)) OR ((orders.id=96281) AND (vendor.id=259)) OR ((orders.id=96282) AND (vendor.id=259)) OR ((orders.id=96284) AND (vendor.id=259)) OR ((orders.id=96286) AND (vendor.id=259)) OR ((orders.id=96287) AND (vendor.id=259)) OR ((orders.id=96289) AND (vendor.id=259)) OR ((orders.id=96290) AND (vendor.id=259)) OR ((orders.id=96291) AND (vendor.id=259)) OR ((orders.id=96292) AND (vendor.id=259)) OR ((orders.id=96293) AND (vendor.id=259)) OR ((orders.id=96299) AND (vendor.id=259)) OR ((orders.id=96301) AND (vendor.id=259)) OR ((orders.id=96302) AND (vendor.id=259)) OR ((orders.id=96304) AND (vendor.id=259)) OR ((orders.id=96310) AND (vendor.id=259)) OR ((orders.id=96311) AND (vendor.id=259)) OR ((orders.id=96313) AND (vendor.id=259)) OR ((orders.id=96315) AND (vendor.id=259)) OR ((orders.id=96653) AND (vendor.id=261)) OR ((orders.id=96319) AND (vendor.id=259)) OR ((orders.id=96369) AND (vendor.id=185)) OR ((orders.id=96383) AND (vendor.id=185)) OR ((orders.id=96414) AND (vendor.id=185)) OR ((orders.id=96492) AND (vendor.id=259)) OR ((orders.id=96494) AND (vendor.id=259)) OR ((orders.id=96499) AND (vendor.id=259)) OR ((orders.id=96501) AND (vendor.id=259)) OR ((orders.id=96749) AND (vendor.id=259)) OR ((orders.id=96502) AND (vendor.id=259)) OR ((orders.id=96507) AND (vendor.id=259)) OR ((orders.id=96508) AND (vendor.id=259)) OR ((orders.id=96519) AND (vendor.id=259)) OR ((orders.id=96545) AND (vendor.id=185)) OR ((orders.id=96555) AND (vendor.id=259)) OR ((orders.id=96556) AND (vendor.id=22)) OR ((orders.id=96557) AND (vendor.id=259)) OR ((orders.id=96561) AND (vendor.id=259)) OR ((orders.id=96563) AND (vendor.id=259)) OR ((orders.id=96570) AND (vendor.id=259)) OR ((orders.id=96572) AND (vendor.id=22)) OR ((orders.id=96583) AND (vendor.id=22)) OR ((orders.id=96584) AND (vendor.id=259)) OR ((orders.id=96606) AND (vendor.id=259)) OR ((orders.id=96609) AND (vendor.id=259)) OR ((orders.id=96610) AND (vendor.id=259)) OR ((orders.id=96611) AND (vendor.id=259)) OR ((orders.id=96616) AND (vendor.id=259)) OR ((orders.id=96626) AND (vendor.id=259)) OR ((orders.id=96627) AND (vendor.id=236)) OR ((orders.id=96631) AND (vendor.id=259)) OR ((orders.id=96634) AND (vendor.id=259)) OR ((orders.id=96655) AND (vendor.id=236)) OR ((orders.id=96656) AND (vendor.id=22)) OR ((orders.id=96672) AND (vendor.id=22)) OR ((orders.id=96683) AND (vendor.id=236)) OR ((orders.id=96687) AND (vendor.id=259)) OR ((orders.id=96688) AND (vendor.id=236)) OR ((orders.id=96705) AND (vendor.id=22)) OR ((orders.id=96706) AND (vendor.id=261)) OR ((orders.id=96734) AND (vendor.id=259)) OR ((orders.id=96742) AND (vendor.id=22)) OR ((orders.id=96743) AND (vendor.id=298)) OR ((orders.id=96752) AND (vendor.id=22)) OR ((orders.id=96753) AND (vendor.id=22)) OR ((orders.id=96754) AND (vendor.id=22)) OR ((orders.id=96757) AND (vendor.id=259)) OR ((orders.id=96768) AND (vendor.id=259)) OR ((orders.id=96778) AND (vendor.id=259)) OR ((orders.id=96782) AND (vendor.id=259)) OR ((orders.id=96783) AND (vendor.id=259)) OR ((orders.id=96786) AND (vendor.id=259)) OR ((orders.id=96787) AND (vendor.id=259)) OR ((orders.id=96789) AND (vendor.id=259)) OR ((orders.id=96790) AND (vendor.id=259)) OR ((orders.id=96792) AND (vendor.id=259)) OR ((orders.id=96793) AND (vendor.id=259)) OR ((orders.id=96817) AND (vendor.id=236)) OR ((orders.id=96820) AND (vendor.id=22)) OR ((orders.id=96846) AND (vendor.id=22)) OR ((orders.id=96858) AND (vendor.id=22)) OR ((orders.id=96879) AND (vendor.id=22)) OR ((orders.id=96890) AND (vendor.id=22)) OR ((orders.id=96898) AND (vendor.id=259)) OR ((orders.id=96903) AND (vendor.id=259)) OR ((orders.id=96905) AND (vendor.id=22)) OR ((orders.id=96921) AND (vendor.id=22)) OR ((orders.id=96938) AND (vendor.id=22)) OR ((orders.id=97043) AND (vendor.id=236)) AND orders.order_status <> 'CAN'
GROUP BY order_details.vendor_fk, orders.id  
ORDER BY `orders`.`order_status`  ASC

CodePudding user response：

Please try by changing the below condition

{

SELECT orders.id,
       orders.po_number,
       orders.placed_date,
       order_details.vendor_fk,
       vendor.vendor_name,
       orders.payment_method,
       orders.order_status,
       MAX(shipped_date) AS shipped_date
  FROM orders
 INNER JOIN order_details
    ON (order_details.order_fk = orders.id and orders.order_status <> 'CAN')

}

CodePudding user response：

I bet its due to the missing brackets on the OR condition being used .

Here is how to correct it.

SELECT orders.id, orders.po_number, orders.placed_date, order_details.vendor_fk, vendor.vendor_name, orders.payment_method, orders.order_status
     , MAX(shipped_date) AS shipped_date 
 FROM orders 
 INNER JOIN order_details 
   ON order_details.order_fk=orders.id 
 INNER JOIN vendor 
   ON vendor.id=order_details.vendor_fk 
WHERE ( --Added the bracket for fixing
      ((orders.id=95837) AND (vendor.id=259)) 
        OR ((orders.id=95838) AND (vendor.id=259)) 
        OR ((orders.id=95841) AND (vendor.id=259)) 
        OR ((orders.id=95842) AND (vendor.id=259)) 
        OR ((orders.id=95845) AND (vendor.id=259)) 
        OR ((orders.id=95846) AND (vendor.id=259)) 
        OR ((orders.id=95848) AND (vendor.id=259)) 
        OR ((orders.id=95851) AND (vendor.id=259)) 
        OR ((orders.id=95853) AND (vendor.id=259)) 
        OR ((orders.id=95859) AND (vendor.id=259)) 
        OR ((orders.id=95860) AND (vendor.id=259)) 
        OR ((orders.id=95862) AND (vendor.id=259)) 
        OR ((orders.id=95863) AND (vendor.id=259)) 
        OR ((orders.id=95864) AND (vendor.id=259)) 
        OR ((orders.id=95865) AND (vendor.id=259)) 
        OR ((orders.id=95866) AND (vendor.id=259)) 
        OR ((orders.id=95868) AND (vendor.id=259)) 
        --add the rest of the OR conditions...
      ) --Added the bracket as fix
  AND orders.order_status <> 'CAN'
GROUP BY order_details.vendor_fk, orders.id  
ORDER BY `orders`.`order_status`  ASC