How to get records for a column having a value but not other-CodePudding

I have a dataset as follows:

SELECT * FROM all_trans LIMIT 6

 --------------- ------------- ----------- ------------------ ----------------- ----------- ---------- 
| transaction_id|card_event_id|card_pos_id|card_point_country|transaction_label|module_name| post_date|
 --------------- ------------- ----------- ------------------ ----------------- ----------- ---------- 
|0P2117055584563|   2480330365|   E3KB2938|                CZ|       FUN0402007|      regex|2022-01-17|
|0P2117055584563|   2480330365|   E3KB2938|                CZ|            FUN04|        mcc|2022-01-17|
|0P2117057388514|   2480338194|   E3KB2938|                CZ|       FUN0402007|      regex|2022-01-17|
|0P2117057388514|   2480338194|   E3KB2938|                CZ|            FUN04|        mcc|2022-01-17|
|0P2117058209585|   2480339519|   E3KB2938|                CZ|       FUN0402007|      regex|2022-01-17|
|0P2117058209585|   2480339519|   E3KB2938|                CZ|            FUN04|        mcc|2022-01-17|

I want to get the transactions with no 'regex' value in module_name (either with 'mcc' value or none whatsoever).

I tried:

SELECT a.transaction_id
FROM all_trans a
    JOIN all_trans b ON a.transaction_id = b.transaction_id
WHERE a.module_name='mcc' AND b.module_name<>'regex'
LIMIT 100

But still getting transactions with both mcc and regex values in the column module_name.

CodePudding user response：

Do a GROUP BY, and use a case expression in the HAVING clause to make sure there is no regex value.

SELECT transaction_id
FROM all_trans
GROUP BY transaction_id
HAVING COUNT(case when module_name = 'regex' then 1 end) = 0
LIMIT 100

Will return distinct transaction_id's.

CodePudding user response：

You can use LEFT JOIN statement with condition like:

SELECT a.transaction_id
FROM all_trans a
LEFT JOIN all_trans b ON 
    a.transaction_id = b.transaction_id AND b.module_name = 'regex'
WHERE a.module_name='mcc' AND b.transaction_id IS NULL
LIMIT 100

WHERE ... b.transaction_id IS NULL remove from list transactions that have 'regex'

Another option as mentioned by @Egrest Basha is using NOT EXISTS condition:

SELECT a.transaction_id
FROM all_trans a
WHERE a.module_name='mcc' AND NOT EXISTS (
    SELECT 1 FROM all_trans b WHERE a.transaction_id = b.transaction_id AND b.module_name = 'regex'
)
LIMIT 100