Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
orlando876
Starting Member
15 Posts |
Posted - 2008-10-13 : 11:15:46
|
I have an assign retrieving only names that was never late for a periodI have to tables Personnel and AttendancePersonnel studentId….NAttendanceStudentidStatus (Late or Early)….N1I want to return from the database only those individual that HAVE Never been late |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-10-13 : 11:18:48
|
[code]SELECT p.*FROM Personnel pINNER JOIN (SELECT Studentid FROM Attendance GROUP BY Studentid HAVING SUM(CASE WHEN Status='Late' THEN 1 ELSE 0 END)=0)aON a.Studentid=p.studentId[/code] |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-10-13 : 11:29:16
|
[code]SELECT p.*FROM Personnel AS pLEFT JOIN Attendance AS a ON a.StudentID = p.StudentID AND a.Status = 'Late'WHERE a.StudentID IS NULL[/code] E 12°55'05.63"N 56°04'39.26" |
|
|
orlando876
Starting Member
15 Posts |
Posted - 2008-10-13 : 11:49:51
|
quote: Originally posted by visakh16
SELECT p.*FROM Personnel pINNER JOIN (SELECT Studentid FROM Attendance GROUP BY Studentid HAVING SUM(CASE WHEN Status='Late' THEN 1 ELSE 0 END)=0)aON a.Studentid=p.studentId
I thank u for the code but i am getting an error for the Sum function not sure what to do |
|
|
orlando876
Starting Member
15 Posts |
Posted - 2008-10-13 : 11:52:21
|
quote: Originally posted by visakh16
SELECT p.*FROM Personnel pINNER JOIN (SELECT Studentid FROM Attendance GROUP BY Studentid HAVING SUM(CASE WHEN Status='Late' THEN 1 ELSE 0 END)=0)aON a.Studentid=p.studentId
I have systax error(missing operator)in query experssion 'SUM(CASE WHEN Status='Late' THEN 1 ELSE 0 END)=0)' |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-10-13 : 11:56:56
|
Put an "AS" before table alias. Some tools need that.SELECT p.*FROM Personnel as pINNER JOIN ( SELECT Studentid FROM Attendance GROUP BY Studentid HAVING SUM(CASE WHEN Status = 'Late' THEN 1 ELSE 0 END) = 0 ) as a ON a.Studentid = p.studentId Or try out my suggestion. E 12°55'05.63"N 56°04'39.26" |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
|
|
|