|
|
Posted by Ed Murphy on 12/24/07 20:48
ll wrote:
> SELECT AMS_CourseCategory.CourseCatDesc,
> AMS_CourseCategory.CourseCatID, AMS_Courses.Class,
> AMS_Courses.Semester, AMS_Courses.TemplateVersion,
> AMS_Courses.YearInProgram, AMS_Courses.CourseID,
> AMS_Courses.Class,AMS_Courses.Semester, AMS_Courses.YearTaught,
> AMS_ContentOverviewObjectiveOutcome.CourseID,
> AMS_ContentOverviewObjectiveOutcome.Objective,
> AMS_ContentOverviewObjectiveOutcome.Out1,
> AMS_ContentOverviewObjectiveOutcome.Out9,
> AMS_ContentOverviewObjectiveOutcome.Out8,
> AMS_ContentOverviewObjectiveOutcome.OutP15,
> AMS_ContentOverview.Complete FROM AMS_ContentOverviewObjectiveOutcome
> INNER JOIN AMS_Courses ON AMS_ContentOverviewObjectiveOutcome.CourseID
> = AMS_Courses.CourseID INNER JOIN AMS_CourseCategory ON
> AMS_Courses.CourseCatID = AMS_CourseCategory.CourseCatID INNER JOIN
> AMS_ContentOverview ON AMS_Courses.CourseID =
> AMS_ContentOverview.CourseID WHERE Semester = 'FA-SP' OR Semester =
> 'FA' OR Semester = 'SP' AND Out8 = 'N' order by AMS_Courses.CourseCatID
The AND/OR issue has already been answered by others. I just feel
obliged to point out that you've got a ton of redundant verbiage due
to non-use of prefixes. Compare this rewrite:
select cc.CourseCatDesc,
cc.CourseCatID,
c.Class,
c.Semester,
c.TemplateVersion,
c.YearInProgram,
c.CourseID,
c.Class,
c.Semester,
c.YearTaught,
cooo.CourseID,
cooo.Objective,
cooo.Out1,
cooo.Out9,
cooo.Out8,
cooo.OutP15,
co.Complete
from AMS_ContentOverviewObjectiveOutcome cooo
join AMS_Courses c on cooo.CourseID = c.CourseID
join AMS_CourseCategory cc on c.CourseCatID = cc.CourseCatID
join AMS_ContentOverview co on c.CourseID = co.CourseID
where c.Semester in ('FA-SP', 'FA', 'SP')
and cooo.Out8 = 'N'
order by c.CourseCatID
Some stylistic notes:
* c.Semester is redundant (appears twice)
* cooo.CourseID is redundant (always same value as c.CourseID)
* ORDER BY uses c.CourseCatID while SELECT uses cc.CourseCatID
(though the values are always the same)
* Out1, Out9, and Out8 look like a 1NF violation. I don't know
what the deal is with OutP15.
http://en.wikipedia.org/wiki/First_normal_form#Example_2:_Repeating_groups_across_columns
What does the entire AMS_ContentOverviewObjectiveOutcome table
look like? Consider refactoring it, or at least maintaining and
using a view that presents its data in a 1NF-compliant form (then
revising the code gradually over time, until all references to the
non-1NF form are eventually eliminated).
Navigation:
[Reply to this message]
|