SQL Joins

[SteveR01]

If anyone can help with this I would be most grateful

I have two main tables one containing student ID numbers and another with there personal details such a name, gender and date of birth. I have joined these using a left join and get what I want no problem.

I also have other tables such as visits to the school nurse for example. When I do another left join and the student has visited the school nurse more than once I get more than one record in the new dataset for that student. I knew this would happen however when using a left join but all I want is either one record returned from the school nurse table or even better just a count of how many visits that student has made to the school nurse.

Dim cn As New SqlConnection
Dim cm As New SqlCommand
Dim dr As SqlDataReader
Dim strSQL1 As String

strSQL1 = ""
strSQL1 = strSQL1 & "Select "
strSQL1 = strSQL1 & "CMIS_ADMIN.STUD_ADMIN.STUGROUPS.SetId, "
strSQL1 = strSQL1 & "CMIS_ADMIN.STUD_ADMIN.STUGROUPS.StudentId, "
strSQL1 = strSQL1 & "CMIS_ADMIN.STUD_ADMIN.STUGROUPS.GroupId, "
strSQL1 = strSQL1 & "CMIS_ADMIN.STUD_ADMIN.NSTUPERSONAL.Forename, "
strSQL1 = strSQL1 & "CMIS_ADMIN.STUD_ADMIN.NSTUPERSONAL.Surname, "
strSQL1 = strSQL1 & "CMIS_ADMIN.STUD_ADMIN.NSTUPERSONAL.Stusex, "
strSQL1 = strSQL1 & "CMIS_ADMIN.STUD_ADMIN.NSTUPERSONAL.LeftSchool , "
strSQL1 = strSQL1 & "[JPS_Portal].[dbo].[medical_notes].[Public_note] "

strSQL1 = strSQL1 & "From CMIS_ADMIN.STUD_ADMIN.STUGROUPS "

strSQL1 = strSQL1 & "LEFT JOIN CMIS_ADMIN.STUD_ADMIN.NSTUPERSONAL "
strSQL1 = strSQL1 & "ON CMIS_ADMIN.STUD_ADMIN.STUGROUPS.StudentId = CMIS_ADMIN.STUD_ADMIN.NSTUPERSONAL.StudentId "
strSQL1 = strSQL1 & "AND CMIS_ADMIN.STUD_ADMIN.NSTUPERSONAL.SetId = '" & Session("SetId") & "' "

strSQL1 = strSQL1 & "LEFT JOIN [JPS_Portal].[dbo].[medical_notes] "
strSQL1 = strSQL1 & "ON CMIS_ADMIN.STUD_ADMIN.STUGROUPS.StudentId = [JPS_Portal].[dbo].[medical_notes].StudentId "
strSQL1 = strSQL1 & "AND [JPS_Portal].[dbo].[medical_notes].SetId = '" & Session("SetId") & "' "

strSQL1 = strSQL1 & "Where "
strSQL1 = strSQL1 & "CMIS_ADMIN.STUD_ADMIN.STUGROUPS.GroupId = '" & Request.QueryString("GroupId") & "' "
strSQL1 = strSQL1 & "AND CMIS_ADMIN.STUD_ADMIN.STUGROUPS.setId = '" & Session("SetId") & "' "
strSQL1 = strSQL1 & "AND CMIS_ADMIN.STUD_ADMIN.NSTUPERSONAL.LeftSchool = 'N' "

strSQL1 = strSQL1 & "Order By CMIS_ADMIN.STUD_ADMIN.NSTUPERSONAL.Surname, CMIS_ADMIN.STUD_ADMIN.NSTUPERSONAL.Forename"

[Purple]

Hi,

How is the visit to the nurse recorded in the table ? is it one row per visit per student ?

Purple