CODEWITHSUNDEEP
sqloracle-database
korodani
korodani

Reputation: 178

SQL : SELECT something and exclude some LIKE values

In table dba_users I have the following values in USERNAME column:

WZHBIJ
LALA
LOLO
MMISROK1
MMISROK2
MMISROK3
MMISROK4
MMISROK5
MMISROK6
WZHMEJ

I want to write a SELECT statement that would return me only WZHBIJ and WZHMEJ, but I MUST use "NOT IN".

So, I would like to stick to the conception using NOT IN :

 SELECT username FROM dba_users where username NOT IN ('LALA','LOLO') OR username NOT IN LIKE 'MMISROK%'

Upvotes: 0

Views: 2752

Answers (3)

BriteSponge
BriteSponge

Reputation: 1054

Just to give another 'flavour' this time using a CTE;

WITH sub_query
 AS (SELECT username
     FROM   dba_users
     WHERE  username LIKE 'MMIS%'
        OR  username LIKE 'L%L%')
SELECT *
FROM   dba_users du
WHERE  mu.username NOT IN (SELECT username FROM sub_query)

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269443

You can use NOT IN twice:

select u.*
from dba_users u
where u.username not in (select u2.username
                         from dba_users u2
                         where u2.username not in ('WZHBIJ', 'WZHMEJ')
                        );

I don't understand the purpose of such exercises.

Upvotes: 2

apomene
apomene

Reputation: 14389

if you must only use NOT IN you can try:

SELECT username
FROM dba_users
WHERE username NOT IN (
        'LALA'
        ,'LOLO'
        )
    AND username NOT IN (
        SELECT username
        FROM dba_users
        WHERE username LIKE 'MMISROK%'
        )

Upvotes: 1

Related Questions