Simple data extraction from 2 tables

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

General SQL Server Forums

New to SQL Server Programming

Simple data extraction from 2 tables

Author

Topic

splat78423
Starting Member

3 Posts

Posted - 2007-03-22 : 11:16:20

So these are a couple different interpretations I've made of a statement that needs to return students name's with the OSOS field in the attendcode code column of the attendance table on todays date with an attendance.aperiod column equal to 4.

This first one returns no results at all when I should get about 7 rows or so.....

Select Student.name,
attendance.studentid,
attendance.attenddate,
attendance.aperiod,
attendance.attendcode
from Attendance, Student
where AttendDate >= DateAdd(Day, DateDiff(Day, 0, GetDate()), 0)
And AttendDate < DateAdd(Day, DateDiff(Day, 0, GetDate()), 0) + 1
And aperiod = '4'
And AttendCode = 'OSOS'
AND student.name = Attendance.studentid

This one is returning all of the names in the database 6 times each.

Select Student.name,
attendance.studentid,
attendance.attenddate,
attendance.aperiod,
attendance.attendcode
from Attendance, Student
where AttendDate >= DateAdd(Day, DateDiff(Day, 0, GetDate()), 0)
And AttendDate < DateAdd(Day, DateDiff(Day, 0, GetDate()), 0) + 1
And aperiod = '4'
And AttendCode = 'OSOS' order by name

I think something is messed up with my WHERE control but I cant figure it out... Any help is greatly appreciated!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-22 : 11:27:05

Yes. You have to tie the two tables attendance and Student together!

use either where attendance.studentid = students.studentid

or

use inner join students on students.studentid = attendance.studentid -- this is to prefer.

Peter Larsson
Helsingborg, Sweden

splat78423
Starting Member

3 Posts

Posted - 2007-03-22 : 11:34:30

I joined them together in the first example but I got no results when tried that.

splat78423
Starting Member

3 Posts

Posted - 2007-03-22 : 11:38:27

I tried the following...

Select Student.name,
attendance.studentid,
attendance.attenddate,
attendance.aperiod,
attendance.attendcode
from Attendance, Student

where attendance.studentid = student.studentid

AND AttendDate >= DateAdd(Day, DateDiff(Day, 0, GetDate()), 0)
And AttendDate < DateAdd(Day, DateDiff(Day, 0, GetDate()), 0) + 1
And aperiod = '4'
And AttendCode = 'OSOS'

order by name

then got 6 results....it appears to have worked! So I guess you were right, I was simply not joining the two tables together correctly!
Thanks so much for the help!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-22 : 11:45:37

See? I am a mind-reader...

You were almost there in your first query. But you tied Student.Name with Attendance.StudentID instead.

Select      s.name,
            a.studentid,
            a.attenddate,
            a.aperiod,
            a.attendcode
from        Attendance AS a
inner join  Student AS s ON s.studentid = a.studentid
WHERE       a.AttendDate >= DateAdd(Day, DateDiff(Day, 0, current_tiemstamp), 0)
            And a.AttendDate < DateAdd(Day, DateDiff(Day, 0, current_timestamp), 1)
            And a.aperiod = '4'
            And a.AttendCode = 'OSOS' 
order by    s.name

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources