CODEWITHSUNDEEP
sql-server
hasan81
hasan81

Reputation: 27

I want to select UserId that contains value and does not contain value

I have a table M_In It has two columns UserId,MeFr I want to select UserId that contains value (123) and does not contain value (456) or other value

Example table M_In

UserId  MeFr
------------
100     *123*
100     *456*
100     *123*
200     *123*
200     *456*
300     *123*

Expected output

UserId  MeFr
------------
300     *123*

I want to use this method Example

Example

Select * from     
  (select i.*,                                   
         row_number() over (partition by UserId order by UserId ) as rn
  from M_In i 
   ) i 
   left join  
   (select o.*,                                   
         row_number() over (partition by UserId order by UserId ) as rn
  from M_In o
  ) o  on o.UserId = i.UserId
 where not EXISTS

Upvotes: 0

Views: 130

Answers (2)

DEEPAK LAKHOTIA
DEEPAK LAKHOTIA

Reputation: 993

A simple join should do the job here:

SELECT m.*
FROM   M_In m
join M_In m1 on m.UserId = m1.UserId
WHERE  m.MeFr = 123 and m1.MeFr <> 123

Upvotes: 1

Squirrel
Squirrel

Reputation: 24763

use make use of NOT EXISTS ()

SELECT *
FROM   M_In m
WHERE  m.MeFr = 123
AND    NOT EXISTS
       (
           SELECT *
           FROM   M_In x
           WHERE  x.UserId = m.UserId
           AND    x.MeFr   <> 123
       )

Upvotes: 2

Related Questions