Glad it worked for you!

You have to know the course numbers in order to exclude departments that
don't teach 400-level courses.  Or maybe I just didn't understand your
question...

--
Jeff Wilson
President
The Boolean Group, Inc.
(xxx) xxx-xxxx
Glendale, CA

<zzz8611@y...> wrote in message news:61735@s..._language...
>
> Jeff, I just tried it. It worked!!!  Thank you very much.  Just wondering
> what to do if you don't know course numbers(regarding this part: "Between
> 400 and 499" in sql statement), just that you don't want any department
> that teach 400 level courses.  Do I have to include more sub queries to
> exclude those courses, or use case when ....? Thanks again.
>
> I vote for the subquery:
> >
> > Select d.*
> > From
> >  Departments d left join
> >   (Select * from Courses Where CourseNumber Between 400 and 499) c
> >  on d.Dcode = c.Dcode
> > Where
> >  c.DCode is NULL
> >
> >
> > --
> >
> > Jeff Wilson
> > The Boolean Group, Inc.
> >
> >
> > <zzz8611@y...> wrote in message news:61274@s..._language...
> > >
> > > Hello SQL gurus:
> > > In a class project of 20 sql questions, we were stopped at one of
> them. It
> > > sounded very simple:"Find out the names of the department that does
not
> > > offer any 400 leverl courses", but yet we can't get the correct result
> in
> > > one query. We know the correct result should be FIN and MKT department
> > > since there is so little data so we can tell just by looking at it.
But
> > > our sql statement is not working out right. We can get the result of
> MKT
> > > since it is not offering any course by using a left join, but FIN is
> > > mixed. It offers 300 level course, no 400 level course, but other
> > > department also offer 300 level courses. So how do we tell sql to work
> > > this out, to exclude other departments that offer both 300 and 400
> level
> > > course(IS and MGT)? subquery or temp table? Is it possible to do it in
> one
> > > query? Thank you everybody!
> > >
> > > There are 2 tables:
> > > Table 1: departments
> > > DCODE DName FID
> > > FIN Finance 035
> > > IS Information Systems 029
> > > MGT Management 047
> > > MKT Marketing 087
> > >
> > > Table2: courses
> > > DCODE CourseNumber CourseTitle FID
> > > FIN 380 Financial Analysis 035
> > > IS 380 Database 047
> > > IS 480 Advanced Database 087
> > > IS 485 System Analysis 023
> > > MGT 380 Human Resources 067
> > > MGT 480 Management Concept 044
> > >
> > > department.dcode= course.dcode
> > > and department.fid = course.fid
> > >
> > >
> > >
> >
> >
>
>