sql operator question

Hi,
I'm working with a SQL statement to select from tables and only return
that which matches the semester AND value of 'N'. The statement below
does return results according to my criteria, but it also returns all
of the other results following the semester criteria that don't have a
value of 'N'.
Thanks for any help you can provide,
Louis
---------------------------------------------

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

Re: sql operator question

On Wed, 19 Dec 2007 15:12:41 -0800 (PST), ll
wrote:

You probably meant to write:
WHERE Semester IN ('FA-SP', 'FA' , 'SP') AND Out8 = 'N'
which is equivalent to:
WHERE (Semester = 'FA-SP' OR Semester =
'FA' OR Semester = 'SP') AND Out8 = 'N'

It's a matter of operator precedence.

-Tom.



>Hi,
>I'm working with a SQL statement to select from tables and only return
>that which matches the semester AND value of 'N'. The statement below
>does return results according to my criteria, but it also returns all
>of the other results following the semester criteria that don't have a
>value of 'N'.
>Thanks for any help you can provide,
>Louis
>---------------------------------------------
>
>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

Re: sql operator question

ll (barn104_1999@yahoo.com) writes:
> I'm working with a SQL statement to select from tables and only return
> that which matches the semester AND value of 'N'. The statement below
> does return results according to my criteria, but it also returns all
> of the other results following the semester criteria that don't have a
> value of 'N'.

> WHERE Semester = 'FA-SP' OR Semester = 'FA' OR Semester = 'SP'
> AND Out8 = 'N'
> order by AMS_Courses.CourseCatID

AND binds tighter than OR, so your WHERE clause says:

WHERE Semester = 'FA-SP' OR
Semester = 'FA' OR
(Semester = 'SP' AND Out8 = 'N')
order by AMS_Courses.CourseCatID

You need to add parentheses:

WHERE (Semester = 'FA-SP' OR Semester = 'FA' OR Semester = 'SP')
AND Out8 = 'N'
order by AMS_Courses.CourseCatID

In this case you could also use the IN operator:

WHERE Semester IN ('FA-SP', 'FA', 'SP')
AND Out8 = 'N'
order by AMS_Courses.CourseCatID



--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx

Re: sql operator question

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:_Re peating_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).

Re: sql operator question

On Dec 24 2007, 2:48 pm, Ed Murphy wrote:
> 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:_Re peating_g...
>
> 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).

Thanks for all the input! Yes, the grouping was the answer. In
revising the code, I'm sure I'll consider the prefixes at some point
in the stage.