How can I select a result in mysql except a selected result in the query

user_16644035 0
Viewed 35

I have a problem. I don't know how to solve it.

I have a Datatable like:

bookingCode status
ABC NEW
ABC CANCELED
DEF CANCELED

If I try to select bookingCode where status = 'CANCELED' I will get ABC and DEF as result. But ABC has a NEW Order. I want only CANCELED where is only one bookingCode. The result has to be only DEF.

How can I solve this problem?

mysql sql
2 Answers

You may use NOT IN or NOT EXISTS or even a group by and filter in your having clause eg

Query #1

SELECT
    bookingCode
FROM
    my_table
WHERE
    status='CANCELED' AND bookingCode NOT IN (
        SELECT bookingCode FROM my_table WHERE status = 'NEW'
    );
bookingCode
DEF

Query #2

SELECT
    bookingCode
FROM
    my_table t1
WHERE
    status='CANCELED' AND NOT EXISTS (
        SELECT 1 
        FROM my_table t2 
        WHERE status = 'NEW' AND t1.bookingCode = t2.bookingCode
    );
bookingCode
DEF

Query #3

SELECT
    bookingCode
FROM
    my_table
GROUP BY
    bookingCode
HAVING
    SUM(
        CASE WHEN status='CANCELED' THEN 1 ELSE 0 END
    ) > 0 
    AND
    SUM(
        CASE WHEN status='NEW' THEN 1 ELSE 0 END
    ) = 0 ;
bookingCode
DEF

or only retrieve bookingcodes where status is cancelled

Query #4

SELECT
    bookingCode
FROM
    my_table
GROUP BY
    bookingCode
HAVING
    SUM(
        CASE WHEN status='CANCELED' THEN 1 ELSE 0 END
    ) = COUNT(bookingCode) ;
bookingCode
DEF

View on DB Fiddle

Let me know if this works for you.

user_43071360

Use a subquery that finds booking codes that are different from CANCELED, and then use NOT IN to exclude those codes from the results.

SELECT bookingCode
FROM bookings
WHERE status = 'CANCELED'
AND bookingCode NOT IN (
    SELECT bookingCode
    FROM bookings
    WHERE status != 'CANCELED'
)
user_14918950
Related