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`) )
subProdIncompID all exist as subProdIDs in
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