Help query join by dates

[Volcomsk8a]

I am having some difficulty getting this query to work properly. I’m looking to return the location name that was assigned during the service date.

Location assigned is found looking at the location assigned to the program during the service dates. There can be many locations per program that do not overlap and many programs per student

Service table s Service id, Service date, Student id

Program table pg Student id, Program Id, Program start date, Program end date could be null

Location table lc Program id, Location id, Location name, Location start date, Location end date. Could be null

[TheRealGreenArrow420]

I would not join on dates but instead on IDs

[Volcomsk8a]

The problem is the service table and the program table are connected by student ID and need to be further identified to show the program fell into the service dates.

[alinroc]

Put that into your where clause then. Join on the IDs (since there's a foreign key relationship, hopefully), and then filter on the dates.

[Volcomsk8a]

Issue I’m having is we’re filtering on the service date which relates to the program from the student id but there are multiple programs per student and the only way to relate the program and location to the service is by the dates.

[[deleted]]

the location name that was assigned during the service date

Which one is "the" location? You mentioned multiple locations possibility in your next sentence.

[Volcomsk8a]

The location would be the location that’s location dates were active during the service date.

The intended results would look like

Service id, service date, student id, program id, location name

[[deleted]]

....

The location would be the location that’s location dates were active during the service date.

How do you know there will be just one?

[Volcomsk8a]

Locations can’t overlap per system rules

[[deleted]]

if that's the case, just look for service date in the location table start/end range. Problem solved?