If Record Exists - SQL Server Forums

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 2008 Forums

Transact-SQL (2008)

If Record Exists

Author

Topic

roger00713
Starting Member

11 Posts

Posted - 2010-11-07 : 22:36:26

Hi.

Can someone please point me in the right direction with my following problem.

I have 2 tables. One contains a list of all students at a school. The other a list of all students that were absent on a particular day.

I want to create a query that lists all students and has a "Yes" or "No" in an absent field for any given day.

Im not sure if im just having one of those days but my brain doesn't seem to be working too well today.

Thanks for any help in advance.

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-11-08 : 01:09:44

Can you post the DDL for two tables so that we don't have to guess table and column names

Sachin.Nand

2937 Posts

Posted - 2010-11-08 : 04:31:39

Is it possible that there can be any other value except "Yes" or "No" for a given student in the absent field?
If yes then your query will be something like this



select * from Student S inner join Attendance A on A.studentId=S.studentId
where A.absent='Yes' or A.absent='No'

where Student table has the student data and Attendance table has the attendance records for a student.

PBUH

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-11-08 : 18:52:48

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Just a guess:

SELECT
    S.StudentName
    ,CASE
        WHEN A.StudentID IS NULL THEN 'Yes'
        ELSE 'No'
     END AS Absent
FROM 
    Student AS S 
LEFT OUTER JOIN
    Attendance AS A 
    ON S.StudentID = A.StudentID
    AND A.AttendanceDate = <SomeDate>

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources