Click here to Skip to main content
15,508,644 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
Hello,
I have 2 tables in an SQL 2008 Server, master and detail, detail table has a date field, I want to check the last record's date field of the detail table of every master record. I want either to add the last record to a NEW datatable or add it to a grid so I can check with the date.now function if the date of each record meets some criteria eg. if from the date.now function date field value is a month forward : date.now = 18 July 2013 and date field value os 18 August 2013 then do something eg display a messagebox. I have made the code for the check date (if need I will post it in VB .Net) but how can I do the selection, is something like this work?

VB
For i = 0 To datatable.Rows.Count - 1
            combobox.Items.Add(datatable.Rows(i).Item("date").ToString)
        Next

maybe it is a dbgrid or something. OR can I use an inner join in an SQL string with an sqldataadapter and add the detail's table last record's datefield to a NEW dataset and do the check? if you can add some SQL code to do it?



CSS
master TABLE : Student
StudentId   StudentName
1           PRASAD
2           RAM
3           RAJ




detail TABLE : Enroll
EnrollId        Course      StudentId   RegesterdDate
1               JAVA        1           2014-07-05 09:03:22.330

2               .NET        1           2014-07-07 09:03:22.330

3               HTML        2           2014-07-01 09:03:22.330

4               MVC         2           2014-07-09 09:03:22.330 value I want to select the most recent date of the detail table this is one record of student RAM

5               CSS         3           2014-07-05 09:03:22.330

6               JQUERY      3           2014-07-06 09:03:22.330




Expected Result:
StudentId   StudentName     Course      RegesterdDate
1           RAM             MVC         2014-07-09 09:03:22.330





Thank you
Posted
Updated 10-Jul-14 23:34pm
v6
Comments
Maciej Los 10-Jul-14 15:03pm    
Not clear ;(
OriginalGriff 10-Jul-14 15:11pm    
I had a think about this, and there isn't really enough info here to be precise.
Perhaps what you want to do is forget English for a few minutes and give us short samples of the two tables and what you want to see as the output - that way we can tell where what data is, and how it connects the two tables together?
Use the "Improve question" widget to edit your question and provide better information.
Swinkaran 11-Jul-14 0:00am    
As per my understanding, you want to find out the Course the student recently enrolled to. Am I correct?
Member 3892343 11-Jul-14 4:38am    
yes i want to select the molst recent date of the records of detail table of every master table record, i will try other answers sql also.
ArunRajendra 11-Jul-14 5:20am    
Check my solution hope that's what your are looking out for.

Not sure if this what you mean. In my project I had to list the latest record with data from linked tables. My query was (with names changed), you can add other columns you need:

C#
string query = "select StartTimestamp from Table1, Table2 ";
       query += "where Table1.ID=Table2.ID ";
       query += "and StartTimestamp in (select MAX(StartTimestamp) from Table1)";


You could also use SELECT TOP command after sorting.
 
Share this answer
 
Try this query.

SQL
select a.StudentId,StudentName,Course,RegesterdDate from Student a inner join Enroll  b on a.StudentId = b.StudentId
 Inner join (
 select StudentId,MAX(RegesterdDate) rdate from Enroll
 group by StudentId) as p on p.StudentId = b.StudentId and p.rdate = b.RegesterdDate
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900