Need help with a query

Hi Experts,

This is my query. I have attached the output I am getting.  How can I modify the query to get the userID and name only once.

SELECT C.ID, C.StudentID,c.ClassID, c.InstructorID ,c.TimeID ,(rtrim(U.UserFirstName)) + '' +
ltrim(rtrim(U.UserLastName)) as UserName,
ltrim(rtrim(U.AttendenceDay))  as AttendenceDay FROM CLASSSETUP as C
LEFT OUTER JOIN  USERDETAILS As U on C.StudentID=U.UserID
Where  C.ClassID =15 order by UserName
Query.txt
RadhaKrishnaKiJayaAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
Advertise Here
 
Jim HornConnect With a Mentor Microsoft SQL Server Developer, Architect, and AuthorCommented:
You'll have to define exactly how you wish the query results to appear, as it looks like for each repeated userID it is returning different ID values and other columns, so the query appears to be working as designed.

For example, user Eulisis_MiddleEu, do you want to see ID values 201, 160, 193, or none at all?

If you only want to see StudentID and UserName.. 
SELECT DISTINCT 
   C.StudentID, 
   rtrim(U.UserFirstName)) + '' + ltrim(rtrim(U.UserLastName)) as UserName
FROM CLASSSETUP as C
   LEFT OUTER JOIN  USERDETAILS As U on C.StudentID=U.UserID
WHERE  C.ClassID =15 
ORDER BY U.UserLastName, U.UserFirstName

Open in new window

0
 
Éric MoreauSenior .Net ConsultantCommented:
what do you do with other values like ID, clsid, InsID, ... which are not unique for a single user?
0
 
Manuel FloresCommented:
using group by clause... however you should get rid of ID and TimeID... is this possible?
0
 
RadhaKrishnaKiJayaAuthor Commented:
Thank you.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.