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

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.

CodePudding user response：

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.

CodePudding user response：

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

CodePudding user response：

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:

• https://blogs.oracle.com/mysql/post/intersect-and-except-in-mysql-80
• https://dev.mysql.com/doc/refman/8.0/en/set-operations.html

CodePudding user response：

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.