SELECT statement help

[schoultzy]

Hello everyone,

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.

[Erland Sommarskog]

schoultzy (sc*******@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Mike C#]

BTW I think the standard now is if you want to blast your email to 500
relevant newsgroups, you post to them all at once. That way if someone
answers your question it will show up in all the groups at once. So people
won't spend hours trying to resolve an issue that others have already
resolved. And post some DDL.

"schoultzy" <sc*******@yahoo.com> wrote in message
news:11**********************@r2g2000cwb.googlegro ups.com...

Hello everyone,

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.

[Greg D. Moore \(Strider\)]

"Mike C#" <xx*@yyy.com> wrote in message news:jo***************@fe12.lga...

BTW I think the standard now is if you want to blast your email to 500
relevant newsgroups, you post to them all at once.

Well if you blast it to 500 newsgroups, you'll probably get kicked off your
ISP. ;-)

But, yes generally cross-posting to 2-3 relevant newsgroups is preferred
over multi-posting to the same 2-3.

For the reason you give.