Query to find emails from SELECT FROM VALUES

Question

I'm having a problem with a query that should only bring back a specific result set, however it's returning all values. Essentially what I'm trying to do is update the email column in one table with the values from a list from another, the inner query works in that I can get the correct first/last names but the outer query returns all emails and I only want the emails from the listed values.

SELECT DISTINCT WorkEmail FROM stafftbl WHERE EXISTS  (
SELECT DISTINCT FirstName,LastName -- inner query starts here
FROM assignments
WHERE Email IN  
(SELECT email 
 FROM 
    (VALUES 
    ('[email protected]'),
    ('[email protected]'),
    ('[email protected]'),
    ('[email protected]'),
    ('[email protected]'),
    ('[email protected]'),
    ('[email protected]'),
    ('[email protected]'),
    ('[email protected]'),
    ('[email protected]'),
    ('[email protected]'),
    ('[email protected]'),
    ('[email protected]'),
    ('[email protected]'),
    ('[email protected]') -- there's a lot more than these.
)E(email))                 -- inner query ends here
)

I know that I'm probably using exists wrong, but I know I'm on the right track since I can get the name pairs back from the assignments table, I just don't want back the whole column. Any help would be appreciated.

EDIT:

Yes, there are duplicates in assignments, and WorkEmail doesn't match all Email! I will do an update to the assignments email column if I can get the appropriate emails from the list that will match the first/last names in the stafftbl table.

Answer 1

I suspect that you are missing a correlation condition in the subquery. Without it, the outer query returns all rows from stafftbl as soon as any row in assignments has an email that can be found in the list.

SELECT DISTINCT WorkEmail 
FROM stafftbl s 
WHERE EXISTS  (
    SELECT 1
    FROM assignments a
    WHERE 
        a.Email = s.WorkEmail     -- correlation to the outer query
        AND a.Email IN  (
            SELECT email 
            FROM (VALUES 
                ('[email protected]'),
                ('[email protected]'),
                ('[email protected]'),
                ('[email protected]'),
                ('[email protected]'),
                ('[email protected]'),
                ('[email protected]'),
                ('[email protected]'),
                ('[email protected]'),
                ('[email protected]'),
                ('[email protected]'),
                ('[email protected]'),
                ('[email protected]'),
                ('[email protected]'),
                ('[email protected]')
            ) E(email)
        )
)

Note 1: there is no need to use DISTINCT and to return specific columns from the EXISTS subquery. This expression is meant to check if the subquery returns some record, and the results are basically discarded. I changed it to SELECT 1.

Note 2: I also suspect that you don't need DISTINCT in the outer query (unless there are duplicate emails in stafftbl.

Note 3: as your query is, using table constructor VALUES() in the subquery is unecessary. You could just use IN, as:

AND a.Email IN  ('[email protected]', '[email protected]', ...)