Or maybe something like this:

CREATE TABLE Employee(

EMPID int,

FNAME varchar(100),

LNAME varchar(100))

CREATE TABLE Contact(

EMPID int,

EMAIL varchar(100),

ORDERBY tinyint)

INSERT INTO Employee

VALUES(1, 'John', 'Smith'),

(2, 'Mary', 'Jones'),

(3, 'Peter', 'Parker')

INSERT INTO Contact

VALUES(1, 't@t.com', 1)

,(1, 's@s.com', 2)

--,(2, 'q@.com', 1)

,(2, 'q@.com', 2)

--,(3, 'z@te.com', 1)

--,(3, 'Z@e.com', 2);

SELECT e.EMPID,

e.FNAME,

e.LNAME,

ISNULL( c.EMAIL, 'No Email') EMAIL

FROM Employee e

OUTER

APPLY (SELECT TOP 1 EMAIL

FROM Contact c

WHERE c.EMPID = e.EMPID

ORDER BY ORDERBY) c;

GO

DROP TABLE Employee

DROP TABLE Contact