I have two tables, one is for kits that can contain various sub-products, and another is a list of sub-products that can't be added together in the same kit.
In simplified form:
CREATE TABLE `kits` (
`subProdID` INT(11) NOT NULL DEFAULT '0',
`kitID` VARCHAR(50) NULL DEFAULT NULL,
`kitName` VARCHAR(512) NULL DEFAULT NULL,
PRIMARY KEY (`subProdID`)
);
CREATE TABLE `subProd_incompatible` (
`IncompID` INT(11) NOT NULL DEFAULT '0',
`subProdID` INT(11) NULL DEFAULT NULL,
`subProdIncompID` INT(11) NULL DEFAULT NULL,
PRIMARY KEY (`VersaIncompID`)
)
In subProd_incompatible
, subProdID
and subProdIncompID
all exist as subProdIDs in kits
.
Right now I use procedural code outside the database to produce a list of kits that all contain products that do not conflict according to the subProd_incompatible
table, but I'd like to do it in SQL if at all possible.
What I'd like is to
SELECT `kitID`, `kitName`, GROUP_CONCAT(`subProdID`)
FROM ("a subquery") AS Q
GROUP BY `GroupingCriterion`
"a subquery" should return the columns of kits
, along with a generated GroupingCriterion
.