Solved

Need help with a query

Posted on 2016-08-08
4
64 Views
Last Modified: 2016-08-08
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
0
Comment
Question by:RadhaKrishnaKiJaya
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 41747457
what do you do with other values like ID, clsid, InsID, ... which are not unique for a single user?
0
 
LVL 5

Expert Comment

by:Manuel Flores
ID: 41747463
using group by clause... however you should get rid of ID and TimeID... is this possible?
0
 
LVL 65

Accepted Solution

by:
Jim Horn earned 500 total points
ID: 41747465
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
 

Author Closing Comment

by:RadhaKrishnaKiJaya
ID: 41747497
Thank you.
0

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Performance in games development is paramount: every microsecond counts to be able to do everything in less than 33ms (aiming at 16ms). C# foreach statement is one of the worst performance killers, and here I explain why.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

761 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question