Search code examples
mysqlrowsrecords

Mysql Query to find specific rows for which the particular column value don't exist

I'm in a very complex situation. I know it can be possible with an sql to achieve my goal. Here what is I'm trying to achieve:

I've just one table named 'data'

I've 4 columns having names orderDate, orderID, orderDescription & orderType

There are 4 types of 'orderType' values exist in my table which are Order, Refund, Returned, Adjustment. The orderid would remain same for each 'OrderType' if any of this case happens.

Now, what I'm trying to achieve is to filter my data as follows.

Select only those orderID records for which the column 'orderType' value (Returned & Adjustment) don't exist in the table.

I'm really looking forward for your help.

Solution 
  • select orderid
from data
group by orderid
having sum(ordertype in ('Returned', 'Adjustment')) = 0
   and sum(ordertype = 'Refund') > 0 
   and sum(ordertype = 'Order') > 0

    or just 

    select orderid
from data
group by orderid
having sum(ordertype not in ('Refund', 'Order')) = 0
and count(distinct ordertype) = 2