SQL Joins

[SteveR01]

If anyone can help with this I would be most grateful

ASP.NET 2.0 and SQL

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 or even better just a count of how many visits a 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"

[e-mail]

[gpinetto]

Hi try with group by or Distinct predicat

[SteveR01]

Thanks for your prompt reply but it did not work. All I really need is how many records there are in the medical table for each student. I don't need any details just how many records.