时间戳

首页 > 解决方案 > 在 sql 中使用子查询。显示无输出

sql - 在 sql 中使用子查询。显示无输出

问题描述

我的任务是检索尚未发货的客户的姓名。下面是得到这个结果的表格↓↓↓</p> 

CREATE TABLE employees
(
    employee_id INT NOT NULL,
    last_name VARCHAR(20) NOT NULL,
    first_name VARCHAR(20) NOT NULL,
    manager_id INT,

    CONSTRAINT employees_pk PRIMARY KEY (employee_id),
    CONSTRAINT emp_fk_mgr 
        FOREIGN KEY (manager_id) REFERENCES employees(employee_id) 
);

CREATE TABLE orders
(
    order_id INT NOT NULL,
    customer_id INT NOT NULL,
    order_date DATE NOT NULL,
    shipped_date DATE,
    employee_id INT,

    CONSTRAINT orders_pk PRIMARY KEY (order_id),
    CONSTRAINT orders_fk_customers
        FOREIGN KEY (customer_id) REFERENCES customers (customer_id),
    CONSTRAINT orders_fk_employees
        FOREIGN KEY (employee_id) REFERENCES employees (employee_id)
);

CREATE TABLE order_details
(
    order_id INT NOT NULL,
    item_id INT NOT NULL,
    order_qty INT NOT NULL,

    CONSTRAINT order_details_pk PRIMARY KEY (order_id, item_id),
    CONSTRAINT order_details_fk_orders
        FOREIGN KEY (order_id) REFERENCES orders (order_id),
    CONSTRAINT order_details_fk_items
        FOREIGN KEY (item_id) REFERENCES items (item_id)
);

目前，我已经完成了以下查询，但它不断生成无输出。

SELECT c.customer_first_name
FROM order_details od
JOIN orders o ON od.order_id = o.order_id
JOIN customers c ON o.customer_id = c.customer_id
WHERE c.customer_id IN (SELECT shipped_date FROM orders WHERE shipped_date IS NULL);

有人可以帮我得到我需要的结果吗？

标签： sqlsubquery

解决方案

据推测，您的客户不是约会对象，而您实际上打算：

WHERE c.customer_id IN (SELECT o.customer_id FROM orders o WHERE shipped_date IS NULL)

推荐阅读