CODEWITHSUNDEEP
sqlsql-server
hussainsajid
hussainsajid

Reputation: 83

How to select times from first table which are not available in the second table?

I've got this problem where if the second table has multiple rows, the query below returns all the fields regardless of what in it.

I've already tried using not between start and end times which works absolutely fine when the second table only has one row in it but as soon as I add another row, it returns everything.

declare @firstTable table (time time(0))
insert @firstTable(time) values ('08:30'),('09:45'),('11:00'),('12:15'),('13:30'),('14:45'),('16:00'),('17:15'),('18:30')

declare @secondTable table (startTime time(0), endTime time(0))
insert @secondTable(startTime, endTime) values ('08:30','10:45'),('13:30','17:00')

select distinct f.time
from @firstTable f, @secondTable s
where f.time not between s.startTime and s.endTime

With the code above, the expected solution should be 11:00, 12:15, 17:15 and 18:30 but it returns all of the times.

Upvotes: 2

Views: 94

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270573

I think not exists does what you want:

select *
from @firstTable ft
where not exists (select 1
                  from @secondTable st
                  where ft.time >= st.startTime and ft.time <= st.endTime
                 );

Here is the db<>fiddle.

Upvotes: 3

Related Questions