query help - 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 2000 Forums

Transact-SQL (2000)

query help

Author

Topic

cognos79
Posting Yak Master

241 Posts

Posted - 2006-11-28 : 19:47:41

I do have a student table like this

studentid coursenum coursestarttime courseendtime
121 cs120 11/20/2006 10:30 11:00
121 cs220 11/20/2006 8:00 9:00
121 cs340 11/20/2006 8:00 9:00
123 cs120 11/20/2006 10:30 11:00
123 cs220 11/20/2006 8:00 9:00

I want to find out the ssn of the students who registered for more than one course that is offered at same time. The query output should look like this:

studentid
121
(bc he registered for cs220 and cs340 which is offered at same time).

can anybody help me how to write the query?

Thanks

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2006-11-28 : 20:26:43

Do you mean offered at exactly at the same time or where classes will overlap?

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2006-11-28 : 22:03:10

this smells like homework to me. class names that start with "cs" are a pretty strong indicator.

Why don't you post what you have so far, someone may point you in the right direction.

SqlSpec: a fast and comprehensive data dictionary generator for
SQL Server 2000/2005, Analysis Server 2005, Access 97/2000/XP/2003
http://www.elsasoft.org

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-11-28 : 23:06:41

[code]SELECT DISTINCT S1.STUDENTID
FROM STUDENT S1 JOIN STUDENT S2
ON S1.STUDENTID = S2.STUDENTID
AND S1.COURSENUM <> S2.COURSENUM
AND S1.COURSESTARTTIME = S2.COURSESTARTTIME
AND S1.COURSEENDTIME = S2.COURSEENDTIME[/code]

Harsh Athalye
India.
"Nothing is Impossible"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-29 : 01:47:37

[code]-- prepare test data
declare @test table (studentid tinyint, coursenum varchar(5), coursestarttime smalldatetime, courseendtime smalldatetime)

insert @test
select 121, 'cs120', '11/20/2006 10:30', '11/20/2006 11:00' union all
select 121, 'cs220', '11/20/2006 08:00', '11/20/2006 09:00' union all
select 121, 'cs340', '11/20/2006 09:45', '11/20/2006 10:45' union all
select 123, 'cs120', '11/20/2006 10:30', '11/20/2006 11:00' union all
select 123, 'cs220', '11/20/2006 08:00', '11/20/2006 09:00'

-- do the work
select t1.studentid,
t1.coursenum,
t1.coursestarttime,
t1.courseendtime
from @test t1
inner join @test t2 on t2.studentid = t1.studentid and t2.coursenum <> t1.coursenum
where not (t1.courseendtime < t2.coursestarttime or t1.coursestarttime > t2.courseendtime)[/code]

Peter Larsson
Helsingborg, Sweden

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2006-11-29 : 02:04:50

this isn't related to your question, but it seems to me your table isn't normalized well.

I think there ought to be one table for students, another for courses, and a table inbetween to store the many-many relationship. It seems you have merged the last two into a single table, which means you'll have duped info in there if more than one student takes the same course (in fact there are 2 examples of duped courses in your sample - cs120 and cs220) This gets rather messy if you have hundreds of people in a course, which is common for freshman classes...

just my 2 cents.

SqlSpec: a fast and comprehensive data dictionary generator for
SQL Server 2000/2005, Analysis Server 2005, Access 97/2000/XP/2003
http://www.elsasoft.org

cognos79
Posting Yak Master

241 Posts

Posted - 2006-11-29 : 10:24:26

Thanks Harsh.

cognos79
Posting Yak Master

241 Posts

Posted - 2006-11-29 : 10:25:14

jezemine...can you please tell how should i design the tables...like an rough design structure of the 3 tables.

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2006-11-29 : 15:47:02

quote:
Originally posted by cognos79

jezemine...can you please tell how should i design the tables...like an rough design structure of the 3 tables.

I can help you, but you need to show that you are at least trying first. What do you think would be a good design? I already pretty much gave you the answer, except for the DDL.

take a look in your textbook on many-to-many relationships. BOL/msdn also has plenty to say about this topic. you might even try a google search for "sql many-to-many relationship" or something like that

SqlSpec: a fast and comprehensive data dictionary generator for
SQL Server 2000/2005, Analysis Server 2005, Access 97/2000/XP/2003
http://www.elsasoft.org

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources