SQL--Checking for existing records which would be duplicative of a proposed record?-CodePudding

I have a table full of existing student names and their respective .edu email addresses. When a new batch of students enters the school, I generate a "proposed" email address for each student using a standard convention ([email protected]). I'm trying to craft a (single) SQL query that generates the proposed email address AND checks whether that proposal would conflict with an existing student who already has that email address.

SELECT firstname   '_'   lastname   '@school.edu' AS ProposedEmailAddress,

(SELECT emailaddress FROM StudentTable WHERE emailaddress LIKE firstname   '_'   lastname   '@school.edu'       ) AS DuplicateCheck

FROM StudentTable

Running this statement is currently producing an error ("Subquery returned more than 1 value") - which doesn't make sense to me as there should never be more than one current student with a given email address.

NOTE: Prefer solutions that are easy to understand and maintain over more elegant approaches. Thank you!

CodePudding user response：

SELECT 
  firstname   '_'   lastname   '@school.edu' AS ProposedEmailAddress,
  (
    SELECT MAX(emailaddress)
      FROM StudentTable 
     WHERE emailaddress = s.firstname   '_'   s.lastname   '@school.edu'
  )
    AS DuplicateCheck
FROM
  StudentTable  AS s

Uses MAX() to ensure a single returned values, and aliases the student table so it's no longer ambiguous (your subquery checked the email address against its own names, not the external references names).

Alternatively, if on sql server...

SELECT 
  s.firstname   '_'   s.lastname   '@school.edu' AS ProposedEmailAddress,
  duplicate.*
FROM
  StudentTable  AS s
OUTER APPLY
(
  SELECT TOP 1 *
    FROM StudentTable 
   WHERE emailaddress = s.firstname   '_'   s.lastname   '@school.edu'
)
  AS duplicate

Demo with both : https://dbfiddle.uk/ej0afDP0

CodePudding user response：

As @MatBailie corrected, using the TOP 1, LIMIT 1, or MAX() functions can simplify and return only the first value from the subquery.

EDITED :

SELECT firstname   '_'   lastname   '@school.edu' AS ProposedEmailAddress,
       (SELECT TOP 1 emailaddress FROM StudentTable 
        WHERE emailaddress = firstname   '_'   lastname   '@school.edu') AS DuplicateCheck
FROM StudentTable