CODEWITHSUNDEEP
sqlt-sql
Agustus Codes
Agustus Codes

Reputation: 59

sql query to return record that has no duplicate record in same table

I have a table which contains record like this.

S.No   | Name   |  Date     |  Type  
1      | test   | 2014-1-1  |   In
2      | test   | 2014-1-1  |   Out
3      | test12 | 2014-1-1  |   In
4      | test12 | 2014-1-1  |   Out
5      | test123| 2014-1-1  |   In

What is required is an sql query that would return only record that has type=In and no type = out has been done for it on same date. I had tried select date,COUNT(*) as count from dbo.TimeCheckInOut where EmployeeNo='E012' group by Date .This is not working.. how can i return those record whose type is In only

Upvotes: 2

Views: 64

Answers (5)

roman
roman

Reputation: 117380

If you want to get only records that has type=In and no type = out then I suggest to do it like this:

select *
from Table1 as t
where
    t.[Type] = 'In' and
    not exists (
        select *
        from Table1 as tt where tt.Name = t.Name and tt.[Type] = 'Out'
    )

sql fiddle demo

Upvotes: 2

paparazzo
paparazzo

Reputation: 45096

Another approach 

select in.* 
  from table in 
  left join table out
    on out.name = in.name 
   and in.type = 'In' 
   and out.type = 'Out' 
 where out.name is null

Upvotes: 0

redsoxlost
redsoxlost

Reputation: 1235

You can use the below Query..

select * from test a
where a.Type='In'
and not exists (select null from test b  where b.Type='Out' and b.Name=a.Name)

Fiddle Here

Upvotes: 0

TechGirl
TechGirl

Reputation: 488

SELECT * FROM table1 t1
WHERE t1.type = 'IN' AND NOT EXISTS (SELECT * FROM  table1 t2 WHERE t2.type = 'OUT' AND t2.name = t1.name)

The above query will yield the desired output.

SQL Fiddle

Upvotes: 0

Vignesh Kumar A
Vignesh Kumar A

Reputation: 28403

Try this

SELECT Name,Date,Type From
Table 1 Where Type = 'In' And 
              Name Not In (Select Name From Table1 Where Type = 'Out')

Fiddle Demo

O/P

SNO NAME    DATE                            TYPE
------------------------------------------------
5   test123 January, 01 2014 00:00:00+0000  In

Upvotes: 0

Related Questions