Select records with multiple row criteria in sql server

Question

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.

@delbertwah are you only getting the first record of the day? because on your table 1 Tuesday you have two records and only get the first one in your output. — bot
– bot, Commented Nov 2, 2015 at 5:07
use where exists.Ok sorry,Guess i didn't read the last para.Can you through few more sample data where time don't clash and output will be different. — KumarHarsh
– KumarHarsh, Commented Nov 2, 2015 at 7:22

Giorgi Nakeuri · Accepted Answer · 2015-11-02 04:54:24Z

1

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)

edited Nov 2, 2015 at 4:54

answered Nov 2, 2015 at 4:48

Giorgi Nakeuri

35.9k8 gold badges50 silver badges78 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Select records with multiple row criteria in sql server

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related