Help with Query

I have two columns I want to compare, both varchar, and
would be in this format
Subject | Instructor
ACCT101-nnnnn | jcdoe
ACCT101-nnnnn | jcdoe
ACCT102-nnnnn | jcdoe
ACCT102-nnnnn | jcsmith
The subject is made up of (Table.Subject + '-' +
CourseNumber)
What I want to do, is query for those cases where the
Table.Subject(ie ACCT101)has the same instructor for each
instance. So if one teacher was teaching the same
section of the course my query would be like this, from
the above example:
Subject | Instructor
ACCT101 | jcdoe
Does anyone know how I could do this?
Thanks.
You could try this:
select
substring(Subject, charindex('-', Subject)) as Subject,
min(Instructor) as Instructor
from yourTable
group by substring(Subject, charindex('-', Subject))
having min(Instructor) = max(Instructor
or
select distinct
substring(Subject, charindex('-', Subject)) as Subject,
Instructor
from yourTable
where not exists (
select * from yourTable Tcopy
where substring(Tcopy.Subject, charindex('-', Tcopy.Subject)) =
substring(yourTable.Subject, charindex('-', yourTable.Subject))
and Tcopy.Instructor <> yourTable.Instructor
)
Just a suggestion. If the two pieces of the [Subject] column have
independent meanings in your table, you might consider keeping them in
separate columns to avoid having to use SUBSTRING to get the information
out.
Steve Kass
Drew University
spacejunk wrote:

>I have two columns I want to compare, both varchar, and
>would be in this format
>Subject | Instructor
>--
>ACCT101-nnnnn | jcdoe
>ACCT101-nnnnn | jcdoe
>ACCT102-nnnnn | jcdoe
>ACCT102-nnnnn | jcsmith
>--
>The subject is made up of (Table.Subject + '-' +
>CourseNumber)
>What I want to do, is query for those cases where the
>Table.Subject(ie ACCT101)has the same instructor for each
>instance. So if one teacher was teaching the same
>section of the course my query would be like this, from
>the above example:
>Subject | Instructor
>--
>ACCT101 | jcdoe
>--
>Does anyone know how I could do this?
>Thanks.
>
>