From: T.R. Henigson
Date: March 3 2000 6:57pm
Subject: RE: tricky SQL needs expert...
List-Archive: http://lists.mysql.com/mysql/30014
Message-Id: <006001bf8542$44a39450$02fea8c0@u3>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: 7bit

> > 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