SQL how to do an outer join properly

Question

I have a table filled with the phone numbers of a company. My problem is, we have a row for every number an employee have, so if he have a main phone and a mobile phone, we have two rows of him.

Now I need to show a list of all employees with their phone number and mobile number (if he have) and the email, but the email is in another table. Im using SQL.

An example:

PhoneTable

   ID  | EmpID  |  FullName     |  Number        |  Type     |
----------------------------------------------------------------
   115 |    02  |  ManuelSan    | +34935556663   |   Fix     |
   116 |    02  |  ManuelSan    | +34652315453   |   Mobile  |
   117 |    06  |  Camillete    | +34934445621   |   Fix     |
   118 |    07  |  MarcusEsq    | +34932547841   |   Fix     |
   119 |    08  |  FionaYem     | +34965214785   |   Fix     |
   120 |    08  |  FionaYem     | +34652132124   |   Mobile  |

EmailTable

    ID   |   empID   |   Fullname   |        Email           |
-----------------------------------------------------------------
   25    |    02     |  ManuelSan   |  [email protected]      |
   26    |    06     |  Camillete   |  [email protected]      |
   27    |    07     |  MarcusEsq   |  [email protected]      |
   28    |    08     |  FionaYem    |  [email protected]       |

So I want this output

   Fullname |    Fix       |     Mobile    |      Email  
------------------------------------------------------------------
 ManuelSan  | +34935556663 | +34652315453  |  [email protected]
 Camillete  | +34934445621 |      NULL     |  [email protected]
 MarcusEsq  | +34932547841 |      NULL     |  [email protected]
 FionaYem   | +34965214785 | +34652132124  |  [email protected]

But im doing this:

SELECT distinct telf.Fullname, telf.Number, acti.EMAIL
  FROM PhoneTable telf
  left outer join EmailTable as acti on acti.empID = telf.empID

I know I need to do something else but I dont know what. Im getting two rows per employee if him have a phone and a mobile.

How should I do that?

Regards,

Answer 1

A lot of redundant data, which may be inconsistent; yet if we assume that empid defines the name and that PhoneTable contains an entry for every employee, the query could look as follows. The first part of the query combines the different records for one employee into one record; the left outer join then gets the corresponding e-mail. Note that the query is still not complete if there are employees that have an e-mail but not even a single phone number:

select *
from (select empid,
          max(fullname),
          max(case when type='Fix' then Number else NULL end) as fix,
          max(case when type='Mobile' then Number else NULL end) as Mobile
      from PhoneTable
      group by empid) phone
  left outer join EMailTable e on phone.empid=e.empid

Answer 2

try this:

select
e.FullName,
t1.Number as "Fix",
t2.Number as "Mobile",
em.Email
from
(
    select distinct
    EmpId,
    FullName
    from PhoneTable
) e
left join PhoneTable t1 on t1.EmpId = e.EmpId and t1.Type = 'Fix'
left join PhoneTable t2 on t2.EmpId = e.EmpId and t2.Type = 'Mobile'
left join EmailTable em on em.empID = e.EmpId