基于关联表查询同时包含'sarah'和'Phillip'的区域的area与job title

Got it, let's work through this problem together. Here are a couple of solid approaches to get exactly the data you're looking for:

First, we need to pinpoint which areas have both 'sarah' and 'Phillip' in table1, then link those areas to table2 to pull in their corresponding job titles.

Method 1: Grouped Filter + Join #

This method uses a subquery to narrow down the qualifying areas first, then joins with table2:

SELECT t2.area, t2.`job title`
FROM table2 t2
INNER JOIN (
    -- Subquery to find areas that have both target names
    SELECT area
    FROM table1
    WHERE name IN ('sarah', 'Phillip')
    GROUP BY area
    -- Ensure both unique names are present in the area
    HAVING COUNT(DISTINCT name) = 2
) t1_qualified ON t2.area = t1_qualified.area;

Breakdown:

• The subquery filters table1 to only rows for our two target names, then groups by area. The HAVING COUNT(DISTINCT name) = 2 check ensures the area has both people (this avoids false positives where one name appears multiple times but the other is missing).
• We then join this list of valid areas with table2 to get the matching job titles.

Method 2: Dual EXISTS Checks #

If you prefer a more explicit approach (which can be efficient in some database setups), use two EXISTS clauses to verify both names exist in the area:

SELECT t2.area, t2.`job title`
FROM table2 t2
WHERE EXISTS (
    -- Check if 'sarah' is present in this area
    SELECT 1 FROM table1 t1
    WHERE t1.area = t2.area AND t1.name = 'sarah'
)
AND EXISTS (
    -- Check if 'Phillip' is present in this area
    SELECT 1 FROM table1 t1
    WHERE t1.area = t2.area AND t1.name = 'Phillip'
);

Breakdown:

• For each row in table2, this query checks two things: does a row exist in table1 for this area with name 'sarah'? And does another row exist for the same area with name 'Phillip'? Only areas that pass both checks are included in the final results.

Either method will reliably return the areas that have both individuals, along with their associated job titles from table2.

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