如何在SQL Server中使用JOIN有效地选择嵌套的依赖表 [英] How to effeciently SELECT Nested dependency Tables using JOIN in SQL Server

问题描述

我有一个父表"Employee"，"Employee Information"存储在3个子表中，每个子表都有一个子表.请考虑以下表格

I'm having One Parent Table "Employee", the Employee Information is stored in 3 Children Tables and each children table has one children Table. Consider the following Tables

表:员工(级别1)

EmpId    IsActive
__________________
1        1
2        1
3        1
4        0
5        0
6        1

表:EmployeeEmail(级别2)

Table : EmployeeEmail (Level #2)

EmpEmailId    EmpId    EmailId
______________________________
1             1         1
2             4         3
3             6         4

表:EmailAddress(第3级)

Table : EmailAddress (Level #3)

EmailId    Email
____________________________
1          [e-mail]
2          [e-mail]
3          [e-mail]
4          [e-mail]

表:EmployeePhone(级别2)

Table : EmployeePhone (Level #2)

EmpPhoneId    EmpId    PhoneId
______________________________
1             1         1
2             2         2
3             5         4

表:电话号码(第3级)

Table : PhoneNumber (Level #3)

PhoneId    PhoneNumber
_______________________
1          9912345671
2          9912345672
3          9912345673
4          9912345674

现在，我需要选择有效的员工记录(完整信息)，如果员工有电话号码，则该电话号码应为空，否则应为NULL，我的电子邮件也需要相同.

Now I need to Select the Active Employee Records (Full Information), if the employee has phone number then it should come otherwise it should be NULL, I need the same for Email too.

请协助我如何加入此结构并有效地获取记录?

Kindly assist me how to Join this structure and fetch the records efficiently ?

推荐答案

替代形式:

• 主查询中只有您真正需要的三个表
• 连接表用于通过ON EXISTS(...)将它们粘合在一起:

• only the three tables that you actually need are in the main query
• the junction tables are used to glue them together via ON EXISTS(...):

SELECT e.empid, em.email, pn.phonenumber
 FROM employee e
 LEFT JOIN emailaddress em 
        ON EXISTS ( -- junctiontable
        SELECT * FROM employeeemail ee
        WHERE ee.empid = e.emp_id
        AND ee.empemailid = em.emailid
        )
 LEFT JOIN phonenumber pn 
        ON EXISTS( -- junctiontable
        SELECT * FROM employeephone ep
        WHERE ep.empid = e.empid
        AND ep.phoneid = pn.phoneid
        )
 WHERE e.isactive=1
        ;

这篇关于如何在SQL Server中使用JOIN有效地选择嵌套的依赖表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！