Ok, I've basically got an answer. With regards to the second
question: I first query the database for all packages with
elements not in the set (in this example, everything not in
A, B, C). Then I query the database again for all packages
containing any of the elements in the set BUT where the
packages are not in the set of packages returned in the
first query. For the first question: The SQL is as follows
(provided by Nicholas Prade)
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
That seems to be it. Thanks all,
Ted
>>I posted this to the MySQL list, but I spend an awful lot of
>>time here soooooooooooo, if there are any SQL experts...
>>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
>> 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 ALONG WITH one or more others. I just
>> want the package containing all three elements (assuming
>> there is one).
>>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 whereas I
>> would not want ABCD. Though it contains the elements of
>> interest, it also contains D so I don't want it.
>>I am pretty desperate. I can provide my incomplete SQL, but I
>>think it may be more confusing than helpful.
>>Please cc. me because I am subscribed to the digest. Thanks,
>>Ted
