Question 21902 in Mysql Question posted in
The official MySQL documentation can be found here.

How to write sql query with minus operator to return rows with missing corresponding values? – Mysql

I have transaction table with columns transaction_id, transaction_category and transaction_quantity. Please view the sample data below

transaction_id transaction_category transaction_quantity
112 sale 1000
112 internal 1000
113 sale 1000
114 sale 1000
114 internal 1000
115 sale 1000
115 external 1000
116 sale 1000
116 internal 1000

In the given table, notice transaction_id 112 has both ‘sale’ and corresponding row with same transaction_id and ‘internal’ category. It is the same with transaction_id 114. But the third row with transaction_id 113 does not have another row with ‘internal’ category and transaction_id 115 also doesn’t have a corresponding row with ‘internal’ category (it has external).

I need to get the list of all transactions that have transaction_category of ‘sale’ but does not have a corresponding transaction_category of ‘internal’. So it should return rows with transaction_id of 113 and 115 in the above case. I was attempting to do this with the minus operator but have been hitting a brick wall attempting to do this.

EDIT: The table is about 5000 rows and none of the columns are indexed. Also, there are other categories too. I will update my table.

Tags:

4

Answers


  1. 0

    You may try with conditional aggregation as the following:

    select transaction_id
from table_name
group by transaction_id
having count(case when transaction_category='sale' then 1 end) > 0 and 
       count(case when transaction_category='internal' then 1 end) = 0

    See a demo.

    Login or Signup to reply.
  2. 0 
    SELECT T.TRANSACTION_ID
FROM TRANSACTIONS AS T
WHERE T.transaction_category='SALE'
   EXCEPT --MINUS
SELECT T.TRANSACTION_ID
FROM TRANSACTIONS AS T
WHERE T.transaction_category='INTERNAL'

    May be this one

    Login or Signup to reply.
  3. 0

    Solution for MySQL 8.0.31, which now supports EXCEPT.

    select distinct transaction_id from transaction where transaction_category = 'sale'
except
select transaction_id from transaction where transaction_category = 'internal';

    MINUS is a synonym invented by Oracle and supported for compatibility in some other SQL implementations (but not MySQL).

    I put the distinct in that query because it’s not clear from your description if there can be only one row per category for a given transaction_id.

    See also:

    Login or Signup to reply.
  4. 0

    Like already mentioned in other answers, EXCEPT is absolutely fine.
    Another good option is EXISTS:

    SELECT 
transaction_id
FROM transactions t1
WHERE transaction_category = 'sale'
AND NOT EXISTS 
  (SELECT 1 FROM transactions t2 
    WHERE t1.transaction_id = t2.transaction_id 
    AND t2.transaction_category = 'internal');

    I guess it should be clear we will add a DISTINCT if we only want to get every transaction id once.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search