CODEWITHSUNDEEP
mysqlsql
user3481307
user3481307

Reputation: 35

MySQL select row with NOT LIKE

This is how my table looks like:

SELECT table2.status, table1.title 
FROM table1 RIGHT JOIN table2 ON table1.id = table2.id

 

status    title
EXPIRED   USER A
EXPIRED   USER B
ACTIVE    USER B
EXPIRED   USER C

And I want to get the titles from the users which status is not ACTIVE, I tried:

SELECT table2.status, table1.title 
FROM table1 RIGHT JOIN table2 ON table1.id = table2.id 
WHERE table2.status NOT LIKE 'ACTIVE'

 

status    title
EXPIRED   USER A
EXPIRED   USER B
EXPIRED   USER C

The problem is that User B has a status ACTIVE and EXPIRED and I want a table that shows me everyone who has NO ACTIVE status in the table:

status    title
EXPIRED   USER A
EXPIRED   USER C

Upvotes: 0

Views: 205

Answers (4)

Thorsten Kettner
Thorsten Kettner

Reputation: 94913

Here is a statement that gives you one row per non-active user: 

SELECT 
  CASE 
    WHEN MAX( case when table2.status = 'EXPIRED' then 1 else 0 end ) = 1 THEN 'EXPIRED'
    WHEN MAX( case when table2.status = 'CANCELLED' then 1 else 0 end ) = 1 THEN 'CANCELLED'
    WHEN MAX( case when table2.status = 'NEW' then 1 else 0 end ) = 1 THEN 'NEW'
    ELSE 'NOT ACTIVE'
  END as status
  table1.title 
FROM table1 
INNER JOIN table2 ON table1.id = table2.id 
GROUP BY table1.title
HAVING MAX( case when table2.status = 'ACTIVE' then 1 else 0 end ) = 0;

The trick is here to get all rows, group by user and kind of create a flag if there exists an ACTIVE status for the user. If so, remove the user from the list in the HAVING clause. Then we decide which status to show. Again we use aggregated flags to find out if our preferred flag 'EXPIRED' exists or 'CANCELLED' otherwise etc.

BTW: I removed the right outer join, because it doesn't make sense to me. If it really happens that there are users without an entry in table1, then outer join, but have table2.id in the result columns and group by this instead of by table1.title.

Upvotes: 1

Kickstart
Kickstart

Reputation: 21513

Possibly use an extra outer join, like this:-

SELECT table2.status, table1.title 
FROM table2 
LEFT OUTER JOIN table1 
ON table1.id = table2.id 
LEFT OUTER JOIN table2 table2a
ON table1.id = table2a.id 
AND table2a.status LIKE 'ACTIVE'
WHERE table2.status NOT LIKE 'ACTIVE'
AND table2a.id IS NULL

Join back against table2 again but only for active records and then put a check in the WHERE clause that the joined row for active records is null (ie, not found)

Upvotes: 0

Ravinder Reddy
Ravinder Reddy

Reputation: 24002

Select title where status is not active. 

select * from table_name 
where title not in( select distinct title 
                    from table_name 
                    where status = 'ACTIVE' )

sub query fetches those titles whose status is ACTIVE and the
outer query fetches all records whose title is not in the ACTIVE status.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269883

It would help if you showed your table structure. But here is one way to approach this:

select t1.*
from table1 t1
where not exists (select 1
                  from table2 t2
                  where t2.id = t1.id and
                        t2.status = 'ACTiVE'
                 );

Upvotes: 3

Related Questions