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.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Retrieving only names that never late for period

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 period

I have to tables Personnel and Attendance

Personnel
studentId
….
N

Attendance
Studentid
Status (Late or Early)
….
N1

I 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 p
INNER JOIN (SELECT Studentid
FROM Attendance
GROUP BY Studentid
HAVING SUM(CASE WHEN Status='Late' THEN 1 ELSE 0 END)=0)a
ON a.Studentid=p.studentId[/code]
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-13 : 11:29:16
[code]SELECT p.*
FROM Personnel AS p
LEFT 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"
Go to Top of Page

orlando876
Starting Member

15 Posts

Posted - 2008-10-13 : 11:49:51
quote:
Originally posted by visakh16

SELECT p.*
FROM Personnel p
INNER JOIN (SELECT Studentid
FROM Attendance
GROUP BY Studentid
HAVING SUM(CASE WHEN Status='Late' THEN 1 ELSE 0 END)=0)a
ON 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
Go to Top of Page

orlando876
Starting Member

15 Posts

Posted - 2008-10-13 : 11:52:21
quote:
Originally posted by visakh16

SELECT p.*
FROM Personnel p
INNER JOIN (SELECT Studentid
FROM Attendance
GROUP BY Studentid
HAVING SUM(CASE WHEN Status='Late' THEN 1 ELSE 0 END)=0)a
ON a.Studentid=p.studentId




I have systax error(missing operator)in query experssion 'SUM(CASE WHEN Status='Late' THEN 1 ELSE 0 END)=0)'
Go to Top of Page

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 p
INNER 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"
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-10-14 : 05:36:35
quote:
Originally posted by Peso

Put an "AS" before table alias. Some tools need that.

E 12°55'05.63"
N 56°04'39.26"



It is not only needed for some tools, but it is more reable and error-free
http://sqlblogcasts.com/blogs/madhivanan/archive/2008/09/09/should-alias-names-be-preceded-by-as-part-2.aspx

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page
   

- Advertisement -