[AccessD] Basic Query Design

Jürgen,

The answer is:  relational division.  I went over to SQL Server
Central and asked a basic question about how to accomplish the type of
query you wanted, and Joe Celko, author of SQL for Smarties, suggested
that I look up relational division in the discussion forums and
elsewhere.

One resource I found (
http://www.cs.arizona.edu/people/mccann/research/divpresentation.pdf )
presented this method:

SELECT tblEmployeeCourse.EmployeeID, FirstName & ' ' & LastName FROM
tblEmployeeCourse INNER JOIN tblEmployee ON tblEmployeeCourse.EmployeeID =
tblEmployee.EmployeeID
WHERE CourseID In (1,2,4,5) GROUP BY tblEmployeeCourse.EmployeeID, FirstName
& ' ' & LastName HAVING Count(tblEmployee.EmployeeID) = 4 ORDER BY FirstName
& ' ' & LastName

SELECT DISTINCT EmployeeID
FROM tblEmployeeCourse AS EC1
WHERE NOT EXISTS
  ( SELECT CourseID FROM tblCourse
    WHERE CourseID In (1,2,4,5) AND NOT EXISTS
      ( SELECT * FROM tblEmployeeCourse AS EC2
        WHERE EC2.CourseID = tblCourse.CourseID
        AND EC2.EmployeeID = EC1.EmployeeID))

Kind of a double-negative.  I'm assuming that your Course table is
called tblCourse.

One of the other three methods mentioned in the PDF file is along the
same COUNT lines as the solution Stuart suggested to you.

Regards,

Steve Erbach
Scientific Marketing
Neenah, WI
www.swerbach.com
Security Page: www.swerbach.com/security


On 2/6/06, Jürgen Welz <jwelz at hotmail.com> wrote:
> Hey Steve:
>
> The actual query is a bit more complex than posted as it takes into account
> certification expiry dates and may mix in numerous additional Inclusive and
> Exclusionary conditions, several driven by multi select list boxes (1
> through 8 regional offices, 1 or more of several categories of employee...).
>   The question I posed was to address one aspect of a search that I provide
> for every major form.  I won't have a problem with dupliicate courses in my
> 'count'.  If I mapped bit flags to categories I'd have no trouble
> dynamically setting query by join SQL and denormalizing everything to the
> Employee table with a few external lookups.  But I digress.
>
> Getting back to how one would integrate this with a SQL Server BE; the SQL
> string rowsource for a hit list may join anywhere from 0 to 8 additional
> tables in any combination, an In(list) may require a list of values subquery
> or nested subqueries and require in/exclusive conditions on the main table
> or any of the related tables.  How does anyone to something like this
> without resorting to dynamic SQL?  Do you have 60 stored parameter stored
> procedures that you substitute in as the rowsource as required?
>
> Stuart's posted solution provides a reasonable approach to cleaning up the
> string generation code I have been using to find matches for meeting 'every
> one' in a list on a single field.  I have yet to run some performance tests
> but expect to do so over the next couple of days.
>
> Anyone else have an idea of another way of doing this before I run some
> testing?
>
> Ciao
> Jürgen Welz
> Edmonton, Alberta
> jwelz at hotmail.com
>
>
>
> >From: Steve Erbach <erbachs at gmail.com>
> >
> >Jürgen,
> >
> >I've been working on this off-and-on today.  Your query reminded me
> >that a database I used to work with, Paradox, contained set operations
> >for this very thing:  Which employees have taken every one of the
> >following list of courses?
> >
> >I thought at first that the ALL keyword might do the trick in SQL, but
> >no such luck.  It doesn't look like there's a straightforward
> >set-oriented way to perform this query in SQL.  It would have to be
> >something like what Stuart showed you.  Only thing I wonder about with
> >that query is if an employee took one of the courses more than once
> >but skipped one of the ones you're looking for.  The COUNT would be
> >the same, but your criteria would not be met.
> >
> >Steve Erbach
> >Neenah, WI