CODEWITHSUNDEEP
mysqlsql
mikew
mikew

Reputation: 1754

How to filter based on column values in other rows?

I didn't know a better way to word the title question.

Here's the sample table:

| email | subscription ref num |  state   |
|-------|----------------------|----------|
| [email protected] |         10           | inactive |
| [email protected] |         11           | inactive |
| [email protected] |         12           | inactive |
| [email protected] |         13           |  active  |
| [email protected] |         14           |  active  |
etc

I want to get all emails from the table that do not have an active subscription. I cannot just filter WHERE state=inactve because look at email address [email protected]. That user has both old inactive subscriptions, but also a currently active one.

So for this sample db, I would only want to return email [email protected]. Hope that makes sense.

Can someone help me with the correct query to use?

Upvotes: 2

Views: 2028

Answers (5)

Roberto
Roberto

Reputation: 2185

...that do not have an active subscription:

select distinct email
from yourTable
where email not in 
     (select email from yourTable where state = 'active')

the query explains itself: select distinct emails that don't have active state in any row.

ADDED: You can create an index on state column in mySql. Also, this could be faster:

select distinct email
from yourTable
where not exists 
     (select * from yourTable as helper 
      where state = 'active' and helper.email = yourTable.email )

Upvotes: 4

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

Here's one way...

select email 
from sampletable 
group by email
having max(state) = 'inactive' and min(state) = 'inactive'

Upvotes: 2

Gábor Lőrincz
Gábor Lőrincz

Reputation: 51

select email 
from table
group by email
having max(state) = 'inactive' 
  and count(distinct state) = 1

or without grouping

select t1.email
from table t1
where t1.state = 'inactive'
  and not exists(
    select 1
    from table t2
    where t2.email = t1.email
      and t2.status = 'active'
  )

Upvotes: 0

Pரதீப்
Pரதீப்

Reputation: 93704

Use Having clause to filter the group. Try this

select email 
from yourtable 
group by email
having count(case when state ='inactive' then 1 end) = 1 
   and count(*)= 1

SQLFIDDLE DEMO

Upvotes: 0

Strawberry
Strawberry

Reputation: 33945

SELECT DISTINCT x.* 
           FROM my_table x 
           LEFT 
           JOIN my_table y 
             ON y.email = x.email 
            AND y.state = 'active' 
          WHERE y.id IS NULL;

Upvotes: 0

Related Questions