Table

| EMAIL | Checkout |
| --- | --- |
| [email protected] | 2b |
| [email protected] | 2c |
| [email protected] | 2b |
| [email protected] | 2c |

I want to know how exactly I can query emails which has checkout primarily as 2b

Query I tried

select distinct(email) from table where checkout='2b' and checkout<>'2c'

The output I am getting

| EMAIL | Checkout |
| --- | --- |
| [email protected] | 2b |
| [email protected] | 2b |

Desired output

| EMAIL | Checkout |
| --- | --- |
| [email protected] | 2b |

CodePudding user response：

SELECT T.EMAIL
FROM YOUR_TABLE AS T
GROUP BY T.EMAIL
HAVING MIN(Checkout)='2b' AND MAX(Checkout)='2b'

CodePudding user response：

Look for

SELECT [DISTINCT] email
FROM table t1
WHERE checkout = '2b'
  AND NOT EXISTS ( SELECT NULL
                   FROM table t2 
                   WHERE t1.email <> t2.email
                     AND t2.checkout = '2c' )

CodePudding user response：

I am not sure how correct this is.

I only tested it with the given data What I do here is bring everything that has 2b in and then I exclude all the emails that don't have 2b.

so the following query will first create a list with all the emails with checkout 2B

• [email protected] 2b
• [email protected] 2b

and after that. it will look again in your list and find checkouts without 2B

• [email protected] 2c
• [email protected] 2c

and combine that 2 list together to get

• [email protected] 2b

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=c96c6f7d0a73b298ee8bf12224b68315

hope that helps

CodePudding user response：

Snowflake Answers:

One way todo it is to use QUALIFY, to stripout rows that have a DISCTINCT COUNT of checkouts that is not equal to 1 AND that the checkout is not '2b'

SELECT email, checkout
FROM VALUES
    ('[email protected]','2b'),
    ('[email protected]','2c'),
    ('[email protected]','2b'),
    ('[email protected]','2c')
    t(email, checkout)
QUALIFY count(distinct checkout) over (partition by email)=1 AND checkout='2b';

EMAIL	CHECKOUT
[email protected]	2b

but that can give many duplicate rows, as can been seen with this input:

SELECT email, checkout
FROM VALUES
    ('[email protected]','2b'),
    ('[email protected]','2c'),
    ('[email protected]','2b'),
    ('[email protected]','2b'),
    ('[email protected]','2b'),
    ('[email protected]','2c')
    t(email, checkout)
QUALIFY count(distinct checkout) over (partition by email)=1 AND checkout='2b';

EMAIL	CHECKOUT
[email protected]	2b
[email protected]	2b
[email protected]	2b

So this can be worked around by pushed the COUNT into the SELECT and then using HAVING as the filter.

SELECT email, checkout 
FROM (
    SELECT email, any_value(checkout) as checkout, count(distinct checkout) as cc
    FROM VALUES
        ('[email protected]','2b'),
        ('[email protected]','2c'),
        ('[email protected]','2b'),
        ('[email protected]','2b'),
        ('[email protected]','2b'),
        ('[email protected]','2c')
        t(email, checkout)
    GROUP BY 1
    HAVING cc = 1
) 
WHERE checkout = '2b';

EMAIL	CHECKOUT
[email protected]	2b
[email protected]	2b

OR the use of DISTINCT

SELECT DISTINCT email, checkout
FROM VALUES
    ('[email protected]','2b'),
    ('[email protected]','2c'),
    ('[email protected]','2b'),
    ('[email protected]','2b'),
    ('[email protected]','2b'),
    ('[email protected]','2c')
    t(email, checkout)
QUALIFY count(distinct checkout) over (partition by email) = 1 and checkout = '2b';

EMAIL	CHECKOUT
[email protected]	2b
[email protected]	2b

MySQL:

This should be valid sql:

SELECT DISTINCT email, checkout FROM (
    SELECT  email, checkout, count(distinct checkout) over (partition by email) as cc
    FROM table_name
)
WHERE cc = 1 and checkout = '2b';