SQL如何正确进行外部联接 [英] SQL how to do an outer join properly

问题描述

我有一张桌子，上面满是公司的电话号码. 我的问题是，员工每拥有一个号码，我们都会有一行，因此，如果他有一个主电话和一部手机，我们将有两行.

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.

现在，我需要显示所有员工及其电话号码，手机号码(如果有的话)和电子邮件的列表，但是电子邮件在另一个表中. 我正在使用SQL.

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.

一个例子:

PhoneTable

PhoneTable

   ID  | EmpID  |  FullName     |  Number        |  Type     |
----------------------------------------------------------------
   115 |    02  |  ManuelSan    | [phone]   |   Fix     |
   116 |    02  |  ManuelSan    | [phone]   |   Mobile  |
   117 |    06  |  Camillete    | [phone]   |   Fix     |
   118 |    07  |  MarcusEsq    | [phone]   |   Fix     |
   119 |    08  |  FionaYem     | [phone]   |   Fix     |
   120 |    08  |  FionaYem     | [phone]   |   Mobile  |

EmailTable

EmailTable

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

所以我想要这个输出

   Fullname |    Fix       |     Mobile    |      Email  
------------------------------------------------------------------
 ManuelSan  | [phone] | [phone]  |  [e-mail]
 Camillete  | [phone] |      NULL     |  [e-mail]
 MarcusEsq  | [phone] |      NULL     |  [e-mail]
 FionaYem   | [phone] | [phone]  |  [e-mail]

但是我正在这样做:

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.

我应该怎么做?

此致

推荐答案

大量冗余数据，可能不一致.但是，如果我们假设empid定义了名称，并且PhoneTable包含每个雇员的条目，则查询可能如下所示.查询的第一部分将一个雇员的不同记录合并为一个记录；然后，左外部联接将获得相应的电子邮件.请注意，如果有员工拥有电子邮件但甚至没有一个电话号码，查询仍无法完成:

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

这篇关于SQL如何正确进行外部联接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！