SQL: Select everything in a table not in a sub-query?
This query appears to work well:
-- Every supplier that produces some red or green part
SELECT Suppliers.sid
FROM Suppliers, Catalog, Parts
WHERE Suppliers.sid = Catalog.sid
AND Catalog.pid = Parts.pid
AND (Parts.color = "red" OR Parts.color = "green");
To check it, I'd like to look at every SID that will not be returned by this query, to make sure that they do not produce green or red parts. How can I do this?
This doesn't seem to be working:
SELECT Parts.color
FROM Suppliers, Catalog, Parts
WHERE Suppliers.sid NOT IN (
SELECT Suppliers.sid, Parts.color
FROM Suppliers, Catalog, Parts
WHERE Suppliers.sid = Catalog.sid
AND Catalog.pid = Parts.pid
AND (Parts.color = "red" OR Parts.color = "green")
);
MySQL Error:
Error 1241 (21000): Operand should contain 1 column(s)
What is the right way to go about this?
This is the SQL used to create the tables I'm working with:
SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL';
CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci ;
USE `mydb`;
-- -----------------------------------------------------
-- Table `mydb`.`Suppliers`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`Suppliers` (
`sid` INT NOT NULL ,
`sname` VARCHAR(45) NULL ,
`address` VARCHAR(45) NULL ,
PRIMARY KEY (`sid`) )
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `mydb`.`Parts`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`Parts` (
`pid` INT NOT NULL ,
`pname` VARCHAR(45) NULL ,
开发者_JAVA百科 `color` VARCHAR(45) NULL ,
PRIMARY KEY (`pid`) )
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `mydb`.`Catalog`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`Catalog` (
`cost` INT NULL ,
`pid` INT NOT NULL ,
`sid` INT NOT NULL ,
PRIMARY KEY (`pid`, `sid`) ,
INDEX `fk_Catalog_Parts1` (`pid` ASC) ,
INDEX `fk_Catalog_Suppliers1` (`sid` ASC) ,
CONSTRAINT `fk_Catalog_Parts1`
FOREIGN KEY (`pid` )
REFERENCES `mydb`.`Parts` (`pid` )
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_Catalog_Suppliers1`
FOREIGN KEY (`sid` )
REFERENCES `mydb`.`Suppliers` (`sid` )
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
NOt completely knowing what you are getting at I see a couple of things wrong. First remove the second column inthe subquery, it is unnecessary:
SELECT Parts.color
FROM Suppliers, Catalog, Parts
WHERE Suppliers.sid NOT IN (
SELECT Suppliers.sid FROM Suppliers, Catalog, Parts
WHERE Suppliers.sid = Catalog.sid
AND Catalog.pid = Parts.pid
AND (Parts.color = "red" OR Parts.color = "green")
);
Finally you apear to have a cross join which I supect you don't want. This is a classic example of why you should not use implicit joins. See if this might work better:
SELECT Parts.color
FROM Suppliers
JOIN Catalog on Suppliers.sid = Catalog.sid
JOIN Parts on Catalog.pid = Parts.pid
WHERE Suppliers.sid NOT IN (
SELECT Suppliers.sid
FROM Suppliers
JOIN Catalog on Suppliers.sid = Catalog.sid
JOIN Parts on Catalog.pid = Parts.pid
WHERE (Parts.color = "red" OR Parts.color = "green")
);
Change your subquery to return one column.
old subquery
SELECT Suppliers.sid, Parts.color
FROM Suppliers, Catalog, Parts
WHERE Suppliers.sid = Catalog.sid
AND Catalog.pid = Parts.pid
AND (Parts.color = "red" OR Parts.color = "green"
new subquery
SELECT Suppliers.sid
FROM Suppliers, Catalog, Parts
WHERE Suppliers.sid = Catalog.sid
AND Catalog.pid = Parts.pid
AND (Parts.color = "red" OR Parts.color = "green"
The inner select needs to only return the id
remove: , Parts.color
SELECT Parts.color
FROM Suppliers, Catalog, Parts
WHERE Suppliers.sid NOT IN (
SELECT Suppliers.sid
FROM Suppliers, Catalog, Parts
WHERE Suppliers.sid = Catalog.sid
AND Catalog.pid = Parts.pid
AND (Parts.color = "red" OR Parts.color = "green")
);
精彩评论