CODEWITHSUNDEEP
sql-serverselect
delbertwah
delbertwah

Reputation: 15

Select records with multiple row criteria in sql server

I have a table like this

ID          Day  Start End
----------- ---- ----- -----
1           M    7:30  9:30
2           T    7:30  11:30
3           T    12:30 14:30

And another table like this

Day  Start End
---- ----- -----
M    8:30  11:30
T    8:30  10:30

I want to select all records from the first table that is based from the second table.

Example Result : 

ID          Day  Start End  
----------- ---- ----- -----
1           M    7:30  9:30 
2           T    7:30  11:30

The result is like that because the first 2 records in the 1st table matched the criterion from the second table. The 1st and 2nd row has the same day and the time clashed with the other one.

Upvotes: 0

Views: 128

Answers (1)

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35780

This join will work:

select * 
from t1
join t2 on t1.day = t2.day and t1.end >= t2.start and t1.start <= t2.end

If time columns are varchars you need to cast to time:

select * 
from t1
join t2 on t1.day = t2.day and 
           cast(t1.end as time) >= cast(t2.start as time) and 
           cast(t1.start as time) <= cast(t2.end as time)

Upvotes: 1

Related Questions