|
|
Posted by Erland Sommarskog on 06/26/06 22:32
schoultzy (schoultzy@yahoo.com) writes:
> I am working with the following code:
>
> select section_master.trm_cde, section_master.crs_cde, crs_title,
> crs_capacity,
> crs_enrollment, section_master.udef_3a_1, monday, tuesday, wednesday,
> thursday, friday, begin_time, end_time, bldg_cde, room_cde,
> name_master.last_name
> from section_master left outer join hdx_reg_class_periods on
> section_master.udef_3a_1 = hdx_reg_class_periods.period
> inner join section_schedules on (section_master.crs_cde =
> section_schedules.crs_cde
> and section_master.yr_cde = section_schedules.yr_cde and
> section_master.trm_cde = section_schedules.trm_cde)
> left outer join name_master on section_schedules.professor_id_num =
> name_master.id_num
>
> where section_master.yr_cde = '2006'
> order by section_master.trm_cde, bldg_cde, room_cde,
> section_master.udef_3a_1
>
> Some clarification, section_master.udef_3a_1 holds the class period in
> which a given class occurs. This SELECT statement returns all classes
> and rooms that are taken (currently being used). I would like to turn
> this statement around to get all the classes and rooms that are not
> taken (not currently being used). I also have to take into account
> those course periods that conflict with other course periods. These
> conflicts are listed in a table, hdx_reg_class_period_conflicts, which
> contains to columns: one that lists the course period and one that
> lists the conflict for that period (there can be multiple conflicts for
> a given period). I am hoping that someone out there could help me
> accomplish this as I am a novice when it comes to SQL.
And in which tables are the classes and rooms defined?
It is very difficult to work with tables that I don't know much about.
Not the least when the names are so cryptic as they are in this case.
I would suggest that you start with cleaning up the query, so that
you use aliases rather than the table names as prefix, and that you
then make sure that all columns are prefixed, so that we can see
from which tables they come from.
Even better, post CREATE TABLE statements for all tables (don't
forget the keys) with a short explanation of their purpose, and
INSERT statements with sample data, and the desired result given
the sample. Yes, I gather that this would be quite a lot of code
to post. Then again you need those INSERT statements anyway so that
you can test your final query.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|