> > I am struggling with a query and would appreciate any help I
> > can get. I have a table of elements, some of which are packages
> > for other elements:
> > elementID
> > elementNAME
> > packageID
> >
> > If the packageID is '0' for a given element, then the element
> > is a package. Otherwise, the element is an element, and its
> > package is specified by the packageID.
> >
> > I have two queries I need to make:
> >
> > 1. Given three elements, for example, I need to know the name
> > name of the package which comprises exactly these three
> > elements. I do not want a package that contains only two of
> > these elements. I do not want a package that contains all
> > three of these elements, but also one or more others.
>
> Hello.
> Supposing
> - elementID is unique
> - your table name is: element
> - A,B and C are the 3 element's IDs
> You can try:
>
> SELECT p.elementNAME, COUNT(e.elementID) AS nb
> FROM element p, element e
> WHERE p.packageID='0' AND e.packageID=p.elementID
> AND e.elementID IN (A,B,C)
> GROUP BY p.elementID HAVING nb=3
>
> Note: the problem is quite different if you only know
> the names of the elements... But you did not precise.
>
>
> > 2. Given three elements, I want all packages which contain any
> > combination of these three elements, but never any others.
> > So, if the elements were A, B and C, I would want the the
> > following packages (if they exist) AB, AC, ABC.
>
>
> With the same conditions, you'd only need to change the HAVING:
>
> GROUP BY p.elementID HAVING nb<=3
>
> Hope this helps.
> Nicolas.
Your first answer is exactly what I need. In the second case, I
think I'd run into a problem with your SQL code if, for example,
a package contained elements A, B, and D. I only want packages
with A, B, and/or C but this package (A,B,D) would be returned.
I could handle this with a second query, but I wonder if there's
a way to do it in a single query...
Ted
