Complicated DISTINCT query problem 1

[joshbula]

The query below returns a list of events. Some events have more than one student in them causing them to be duplicated for each student. Some events don't have any students in them, which is why I used the Left Outer joins.

I need to modify it to only return one row for each event, as if the student column wasn't even there--but the student column does need to be there and list at least one student (it doesn't matter which one).

I've tried embedding "TOP 1" and "DISTINCT" queries into the Joins, but always get syntax errors. I saw a previous post earlier that was similar to this with duplicate addresses, but I couldn't figure out how to transfer that solution to my situation.

Any help is greatly appreciate.

Thanks...J

SELECT DISTINCT tblEntriesSE.EntryID, tblSEMusicList.MusicListCode, tblSEMusicList.Title, tblSEMusicList.Composer, 
tblSEMusicCategories.Category, tblEntriesSE.SchoolID, tblEntriesSE.EventID, tblStudents.FirstName + ' ' + 
tblStudents.LastName AS StudentName
 
FROM tblEntriesSE 
INNER JOIN tblSEMusicList ON tblEntriesSE.MusicListCode = tblSEMusicList.MusicListCode 
INNER JOIN tblSEMusicCategories ON tblSEMusicList.CategoryCode = tblSEMusicCategories.Code 
LEFT OUTER JOIN tblSEStudentsInEvents ON tblEntriesSE.EntryID = tblSEStudentsInEvents.EntryID 
LEFT OUTER JOIN tblStudents ON tblSEStudentsInEvents.StudentID = tblStudents.StudentID 

WHERE (tblEntriesSE.EventID = 11) AND (tblEntriesSE.SchoolID = 510331)

 

[gmmastros]

Try this:

SELECT tblEntriesSE.EntryID, tblSEMusicList.MusicListCode, 
       tblSEMusicList.Title, tblSEMusicList.Composer,
       tblSEMusicCategories.Category, tblEntriesSE.SchoolID, 
       tblEntriesSE.EventID, A.StudentName
FROM   tblEntriesSE
       INNER JOIN tblSEMusicList ON tblEntriesSE.MusicListCode = tblSEMusicList.MusicListCode
       INNER JOIN tblSEMusicCategories ON tblSEMusicList.CategoryCode = tblSEMusicCategories.Code
       Left Join (
         Select tblSEStudentsInEvents.EntryID,
                Min(tblStudents.FirstName + ' ' + tblStudents.LastName) AS StudentName
         From   tblSEStudentsInEvents 
                Inner Join tblStudents 
                   ON tblSEStudentsInEvents.StudentID = tblStudents.StudentID 
         Group By tblSEStudentsInEvents.EntryID
         ) As A
         On tblEntriesSE.EntryId = A.EntryId
WHERE (tblEntriesSE.EventID = 11) AND (tblEntriesSE.SchoolID = 510331)

If this works for you, and you want me to explain it, just let me know.


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[joshbula]

That works perfectly... thank you so much!!!! I think it makes sense to me now. I didn't realize it would need to be that complicated, but that makes sense.

Thanks!