I need help with querying a database.
I have two tables: Students and Studies.
Students - StudentID, FirstName, LastName
Studies - StudyID, BeginDate, EndDate, StudentID (FK)
The relation between the tables is a one(Students) to many(Studies). Basically, the 'studies' table describes the dates each student entered and left the School. He could have done that multiple times, so there could be multiple rows for each student.
Now I want to build a query that will return only the students who are currently in School. In order to do that, I need to get the most recent row that applies to that Student (based on the latest 'BeginDate'), and check if the current date is either between the BeginDate and EndDate or that the current date is after the BeginDate and the EndDate is NULL.
STUDENTID FIRSTNAME LASTNAME
1 John Smith
2 Mark Clinton
3 David Bush
STUDYID BEGINDATE ENDDATE STUDENTID
1 1/1/2008 1/1/2009 1
2 1/1/2010 NULL 1
3 1/1/2009 NULL 2
In such a case, I would only want to get 'Mark Clinton'. I wouldn't be interested in 'David Bush' because he has no entry in the Studies table.
So how do I write the query? (I'm using SQL Server Express)
Thank you so much!