根据输入的Buyers.Name查询拥有相同Stores.StoreID的其他Buyers.Name的SQL技术需求

Got it, let's break down how to solve this problem step by step—we need to find other buyers who share at least one store with a specified buyer name.

First, Let's Recap the Data Structure #

We're working with two tables:

• Buyers: Has BuyerID (unique identifier) and Name (the buyer's name)
• Stores: Links buyers to their stores via BuyerID (foreign key to the Buyers table) and StoreID

SQL Query to Get the Desired Results #

Here's a straightforward, join-based query that will do exactly what you need:

SELECT DISTINCT b2.Name
FROM Buyers b1
JOIN Stores s1 ON b1.BuyerID = s1.BuyerID
JOIN Stores s2 ON s1.StoreID = s2.StoreID
JOIN Buyers b2 ON s2.BuyerID = b2.BuyerID
WHERE b1.Name = 'Sten' -- Replace this with your input [Buyers.Name]
AND b2.BuyerID != b1.BuyerID;

How This Query Works (Using Your Example) #

Let's walk through what happens when we input "Sten":

1. Locate the input buyer: b1.Name = 'Sten' finds the row where BuyerID = 1.
2. Fetch their stores: Joining with s1 gives us the StoreIDs linked to Sten: 7 and 13.
3. Find other buyers with those stores: Joining with s2 pulls all BuyerIDs associated with StoreID 7 or 13 (which are 1 and 3).
4. Exclude the input buyer: b2.BuyerID != b1.BuyerID removes Sten (BuyerID 1) from the results.
5. Avoid duplicates: DISTINCT ensures we don't get repeated names if a buyer shares multiple stores with the input.

Alternative Subquery Version (More Readable for Some) #

If you prefer a subquery-focused approach, this works just as well:

SELECT DISTINCT b.Name
FROM Buyers b
JOIN Stores s ON b.BuyerID = s.BuyerID
WHERE s.StoreID IN (
    -- Get all stores linked to the input buyer
    SELECT StoreID
    FROM Stores
    WHERE BuyerID = (
        -- Get the BuyerID matching the input name
        SELECT BuyerID FROM Buyers WHERE Name = 'Sten'
    )
)
AND b.BuyerID != (
    -- Make sure we don't return the input buyer themselves
    SELECT BuyerID FROM Buyers WHERE Name = 'Sten'
);

Testing Your Example #

Run either query with 'Sten' as the input, and you'll get Patrick as the result—perfect, since both Sten and Patrick are connected to StoreID 13.

Key Notes #

• If the input name doesn't exist in the Buyers table, both queries will return an empty result set (which is correct behavior).
• DISTINCT is crucial here—without it, you might get duplicate names if a buyer shares multiple stores with the input buyer.

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