Select Products NOT Bought by a Customer-CodePudding

I have a test database with just three tables. How may I select which products have NOT been bought by CUSTOMER ID = 1? I'm unable to use the NOT IN keywords because in reality each table will be enormous and will exceed the 'IN' limits.

DROP DATABASE IF EXISTS testdata;
CREATE DATABASE IF NOT EXISTS testdata DEFAULT CHARACTER SET = utf8;
USE testdata;
SET character_set_client = utf8;


CREATE TABLE CUSTOMER (
  ID INTEGER NOT NULL AUTO_INCREMENT,     /* Unique Identifier */

  FIRSTNAME VARCHAR(200) DEFAULT '' NOT NULL,   /* Customer Firstname */
  BALANCE INTEGER DEFAULT '0' NOT NULL,       /* Credit balance of customer */

  PRIMARY KEY(ID)
);

insert into CUSTOMER (FIRSTNAME, BALANCE)
  VALUES ('Steve', 100),
  ('Dwayne', 100),
  ('John', 200),
  ('George', 50),
  ('Alice', 100);

CREATE TABLE PRODUCT (
  ID INTEGER NOT NULL AUTO_INCREMENT,     /* Unique Identifier */

  ITEMNAME VARCHAR(200) DEFAULT '' NOT NULL,   /* Product name */
  PRICE INTEGER DEFAULT '0' NOT NULL,       /* Price of product */

  PRIMARY KEY(ID)
) ;

insert into PRODUCT (ITEMNAME, PRICE)
  VALUES
  ('Car', 50),
  ('Lego', 12),
  ('Soup', 3),
  ('Paper', 1),
  ('PC', 60);

CREATE TABLE CUSTOMER_PRODUCT (
  ID INTEGER NOT NULL AUTO_INCREMENT,
  CUSTOMERID INTEGER NOT NULL,              /* Person ID */
  PRODUCTID INTEGER NOT NULL,               /* Product ID */
  PRIMARY KEY(id)
  ) ;

ALTER TABLE CUSTOMER_PRODUCT ADD CONSTRAINT FK_PERS_PROD_PERS FOREIGN KEY (CUSTOMERID) REFERENCES CUSTOMER(ID) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE CUSTOMER_PRODUCT ADD CONSTRAINT FK_PERS_PROD_PROD FOREIGN KEY (PRODUCTID) REFERENCES PRODUCT(ID) ON DELETE CASCADE ON UPDATE CASCADE;

insert into CUSTOMER_PRODUCT (CUSTOMERID, PRODUCTID)
  VALUES (1, 1),
  (1, 2),
  (2, 4),
  (1, 5);

I've attempted with something like this and many variations but cannot get 'Soup' and 'Paper' as the result...

select * from PRODUCT P
right join CUSTOMER_PRODUCT CP on P.ID = CP.PRODUCTID
left join CUSTOMER C on C.ID = CP.CUSTOMERID
where C.ID = 1 
/* and this is where I get stuck! :( */

CodePudding user response：

You can try to let condition C.ID = 1 to ON from where and using LEFT JOIN

select * from PRODUCT P
left join CUSTOMER_PRODUCT CP on P.ID = CP.PRODUCTID
left join CUSTOMER C on C.ID = CP.CUSTOMERID
AND CP.CUSTOMERID = 1 
WHERE C.ID IS NULL

sqlfiddle