SQL子查询需求：查询通过售卖指定书籍员工下单的客户所在城市

Hey there! Let's work through this SQL problem step by step—since you're new to SQL, I'll break down exactly how to get the result you need using subqueries, and fix the issues in your original attempt.

First, let's clarify the core logic we need:

• Identify all employees who sold the book Quickbooks for Business
• Find all customers who placed orders handled by those employees
• Get the cities where those customers live

Your original query had two main issues: you were joining multiple tables with commas without any relationship conditions (which causes a messy Cartesian product), and your subquery was cut off mid-logic. Here's a corrected, working version using nested subqueries:

SELECT DISTINCT z.city
FROM zipcode z
WHERE z.zip IN (
    -- 获取目标员工对应的客户邮编
    SELECT c.zip
    FROM customer c
    WHERE c.customer_id IN (
        -- 获取目标员工处理的订单对应的客户ID
        SELECT o.customer_id
        FROM orders o
        WHERE o.employee_id IN (
            -- 获取售卖过指定书籍的员工ID
            SELECT DISTINCT o2.employee_id
            FROM orders o2
            JOIN orderline ol ON o2.order_id = ol.order_id
            JOIN book b ON ol.book_id = b.book_id
            WHERE b.title = 'Quickbooks for Business'
        )
    )
);

Let's break down each layer of the subquery:

• 最内层子查询：这一步找出所有售卖过《Quickbooks for Business》的员工ID。我们通过orders关联orderline（把订单和具体书籍条目绑定），再关联book表过滤出目标书籍。用DISTINCT避免同一个员工多次售卖该书籍时重复出现ID。
• 第二层子查询：利用内层得到的员工ID，提取所有在这些员工处下单的客户ID。
• 第三层子查询：通过客户ID获取他们对应的邮编信息。
• 最外层查询：最后把邮编映射到zipcode表中的城市名称，DISTINCT用来避免同一个城市重复输出。

小提示：如果你的表字段名和示例不同（比如用ord_id代替order_id，cust_id代替customer_id），记得根据实际表结构调整字段名称哦。

内容的提问来源于stack exchange，提问作者Soft_ibo