SQL Check if the User has IN and Out

I need help getting the User which has an ‘IN’ and ‘Out’ in Column isIN. If the user has an IN and OUT do not select them in the list. I need to select the user who has only had an IN. Please I need help. Thanks in advance.

This is the table:

| Users              |  IsIN |
|:------------------:|:-----:|
| MHYHDC61TMJ907867  |  IN   |
| MHYHDC61TMJ907867  |  OUT  |
| MHYHDC61TMJ907922  |  IN   | 
| MHYHDC61TMJ907922  |  OUT  |
| MHYHDC61TMJ907923  |  IN   |
| MHYHDC61TMJ907923  |  OUT  |
| MHYHDC61TMJ907924  |  IN   |  - I need to get only this row
| MHYHDC61TMJ907925  |  IN   |
| MHYHDC61TMJ907925  |  OUT  | 
| MHYHDC61TMJ908054  |  IN   |  - I need to get only this row
| MHYHDC61TMJ908096  |  IN   |  - I need to get only this row
| MHYHDC61TMJ908109  |  IN   |  - I need to get only this row

Need to get the result like

| Users              | IsIN |
|:------------------:|:-----:|
| MHYHDC61TMJ907924  |  IN   | 
| MHYHDC61TMJ908054  |  IN   |  
| MHYHDC61TMJ908096  |  IN   |  
| MHYHDC61TMJ908109  |  IN   |

I tried using this query and sample query below but it doesn’t work.

select s.[Users], s.[isIn] [dbo].[tblIO] s
where not exists (
    select 1
    from [dbWBS].[dbo].[tblIO] s2
    where s2.[Users] = s.[Users] and s2.isIn = 'IN'
);

Answer

You can use not exists:

select s.*
from sample s
where not exists (select 1
                  from sample s2
                  where s2.user = s.user and s2.inout = 'OUT'
                 );

If you want only users that meet the condition (and not the full rows):

select user
from sample s
group by user
having min(inout) = max(inout) and min(inout) = 'IN';

Advertisement

Answer