Re: Help with Query
From: Steve Kass (skass_at_drew.edu)
Date: 07/20/04
- Previous message: spacejunk: "Help with Query"
- In reply to: spacejunk: "Help with Query"
- Messages sorted by: [ date ] [ thread ]
Date: Tue, 20 Jul 2004 16:18:21 -0400
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.
>
>
>
- Previous message: spacejunk: "Help with Query"
- In reply to: spacejunk: "Help with Query"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
|
