就像标题所说的,我需要查询一个自引用表来查找所有不是任何项的父项。
我的表被附加为一个图像链接:
下面是我的schema:
CREATE TABLE IF NOT EXISTS `recursivebom`.`level` (
`level` INT NOT NULL,
PRIMARY KEY (`level`))
ENGINE = InnoDB;
——表recursivebom
。item
CREATE TABLE IF NOT EXISTS `recursivebom`.`item` (
`itemNumber` INT NOT NULL,
`quantity` DECIMAL NULL,
`cost` DECIMAL NULL,
`parentItem` INT NULL,
`level` INT NOT NULL,
PRIMARY KEY (`itemNumber`, `level`),
INDEX `fk_item_item1_idx` (`parentItem` ASC),
INDEX `fk_item_level1_idx` (`level` ASC),
CONSTRAINT `fk_item_item1`
FOREIGN KEY (`parentItem`)
REFERENCES `recursivebom`.`item` (`itemNumber`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_item_level1`
FOREIGN KEY (`level`)
REFERENCES `recursivebom`.`level` (`level`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
这个怎么样?
SELECT *
FROM item
WHERE itemNumber NOT IN (SELECT parentItem FROM item)