PostgreSQL查询 - 适用于客户和销售代表

Question

q：列出Ann Hull代表但目前没有订单的所有客户的数字和名称。

这是我到目前为止所拥有的...但这不是正确的输出：

CREATE TABLE REP (
  REP_NUM char(2) PRIMARY KEY,
  LAST_NAME char(15),
  FIRST_NAME char(15),
  STREET char(15),
  CITY char(15),
  PROVINCE char(3),
  ZIP char(5),
  COMMISSION decimal(7, 2),
  RATE decimal(3, 2)
);
CREATE TABLE CUSTOMER (
  CUSTOMER_NUM char(3) PRIMARY KEY,
  CUSTOMER_NAME char(35) NOT NULL,
  STREET char(15),
  CITY char(15),
  PROVINCE char(3),
  ZIP char(5),
  BALANCE decimal(8, 2),
  CREDIT_LIMIT decimal(8, 2),
  REP_NUM char(2)
);
CREATE TABLE ORDERS (
  ORDER_NUM char(5) PRIMARY KEY,
  ORDER_DATE date,
  CUSTOMER_NUM char(3)
);
CREATE TABLE PART (
  PART_NUM char(4) PRIMARY KEY,
  DESCRIPTION char(15),
  ON_HAND decimal(4, 0),
  CLASS char(2),
  WAREHOUSE char(1),
  PRICE decimal(6, 2)
);
CREATE TABLE ORDER_LINE (
  ORDER_NUM char(5),
  PART_NUM char(4),
  NUM_ORDERED decimal(3, 0),
  QUOTED_PRICE decimal(6, 2),
  PRIMARY KEY (ORDER_NUM, PART_NUM)
);

Answer 1

SELECT * FROM CUSTOMER c
LEFT JOIN REP r
ON c.REP_NUM = r.REP_NUM
LEFT JOIN ORDERS o
ON o.CUSTOMER_NUM = c.CUSTOMER_NUM 
WHERE o.CUSTOMER_NUM ISNULL
AND r.FIRST_NAME = 'Ann Hull'

希望以上有帮助，它应该对Where子句进行少量修改，特别是结合了第一个和姓氏。

Answer 2

在Ann Hull上过滤rep Table和Ann Hull的LastName，并将其与Rep_num字段上的Rep Table一起加入。仅返回没有订单的客户使用子查询来检查客户的订单表中是否存在(不存在(。

SELECT * FROM CUSTOMER c
JOIN REP r
ON c.REP_NUM = r.REP_NUM
AND r.FIRSTNAME = 'Ann'
AND r.LASTNAME = 'Hull'
AND NOT EXISTS(SELECT * FROM ORDERS o WHERE o.CUSTOMER_NUM = c.CUSTOMER_NUM)