Hello,
I have a table storing bills of materials. I want to select all the products
that contain certain items and don't contain certain other items.
What is the best way? I tentatively guessed at using a HAVING clause but I
was wrong. I think this leaves me with a nasty set of self joins or a less
nasty set of sequential queries:
CREATE CURSOR bom (item C(10), comp C(10))
INSERT INTO bom VALUES ("item1", "comp1")
INSERT INTO bom VALUES ("item1", "comp2")
INSERT INTO bom VALUES ("item1", "comp3")
INSERT INTO bom VALUES ("item1", "comp4")
INSERT INTO bom VALUES ("item1", "comp5")
INSERT INTO bom VALUES ("item2", "comp1")
INSERT INTO bom VALUES ("item2", "comp3")
INSERT INTO bom VALUES ("item2", "comp4")
INSERT INTO bom VALUES ("item2", "comp5")
INSERT INTO bom VALUES ("item3", "comp1")
INSERT INTO bom VALUES ("item3", "comp7")
INSERT INTO bom VALUES ("item3", "comp8")
INSERT INTO bom VALUES ("item3", "comp9")
INSERT INTO bom VALUES ("item3", "comp10")
INSERT INTO bom VALUES ("item3", "comp11")
INSERT INTO bom VALUES ("item3", "comp15")
I want all the items that contain, say,
a ("comp1" or a "comp3")
not a ("comp6" or a "comp7")
and a "comp2"
Any ideas? [it should just be "item1" in the above data]
* this is the kind of thing I'd hoped would work but wasn't too surprised
when it didn't:
SELECT item FROM bom ;
HAVING (comp="comp1" OR comp="comp3") ;
AND !(comp="comp6" OR comp="comp7") ;
AND comp="comp2" ;
GROUP BY item
In my actual data I have to test many more components present / not present
than that example.
I'm thinking a series of EXISTS and NOT EXISTS queries is going to be
easiest at the moment.
--
TIA
Andrew Howell