SQL join related question

Question

Hi guys, I am having trouble with a SQL join question.

I have a table EMPLOYEE with EmpID, FirstName, LastName, Email, Phone

I have another table OTHERNAME with 2 fields "Name" & "OtherName".

This table contains lookup values such as "James", "Jim"; "Thomas", "Tom"; "Steven", "Steve".

I want to write a query which will return rows

EmpID, FirstName, LastName, Email, Phone, OtherName
where Employee.Firstname = OTHERName.Name

Answer 1

Select e.EmpID, e.FirstName, e.LastName, e.Email, e.Phone, o.OtherName
From Employee e
Left Outer Join OtherName o on e.FirstName = o.Name

From your comments it sounds like you actually want an outer join.

(From Comments) An outer join would return all employees, along with an Othername if there is one, otherwise Othername would be a null value which you could handle in code. An inner join limits the results to only employees with a matching record.

Answer 2

SELECT E.EmpID, E.FirstName, E.LastName, E.Email, E.Phone, O.OtherName
FROM Employee E 
    INNER JOIN Othername O
        ON E.Firstname = O.Name

Should do the trick

Or, if you want all results, even those without "other name" values.

SELECT E.EmpID, E.FirstName, E.LastName, E.Email, E.Phone, O.OtherName
FROM Employee E 
    LEFT OUTER JOIN Othername O
        ON E.Firstname = O.Name

Answer 3

SELECT E.EmpId, E.FirstName, E.LastName, E.Email, E.Phone, O.OtherName FROM EMPLOYEE E INNER JOIN OTHERNAME O ON E.FirstName = O.Name

Answer 4

try this:

SELECT
    e.EmpID
        CASE 
            WHEN o.OtherName IS NOT NULL THEN OtherName
            ELSE e.FirstName
        END AS FirstName
        ,e.LastName
        ,e.Email
        ,e.Phone
        ,o.OtherName
    FROM Employee             e
    LEFT OUTER JOIN OtherName o ON e.FirstName = o.Name