> -----Original Message-----
> From: T.R. Henigson [mailto:ted@stripped]
> Sent: vendredi 3 mars 2000 04:37
> To: mysql@stripped
> Subject: tricky SQL needs expert...
>
>
> 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.
> I am pretty desperate. I can provide my incomplete SQL, but I
> think it may be more confusing then helpful.
>
> Please cc. me because I am subscribed to the digest. Thanks,
>
> Ted
>
> --
> ---------------------------------------------------------------------
> Please check "http://www.mysql.com/Manual_chapter/manual_toc.html" before
> posting. To request this thread, e-mail mysql-thread29908@stripped
>
> To unsubscribe, send a message to:
> <mysql-unsubscribe-prade=internetassocies.com@stripped>
>
>
