如何用不连续的连接在SQL中编写表 [英] How to write Tables in SQL with a Disjoint Connection

问题描述

因此，我要创建三个表，它们的连接不相交。他们是人，房客和雇员。
因此，每个人都必须是房客或雇员，而不能两者都做。
我创建一个具有PersonID主键的Person表，并将tenant和Employee作为两个单独的表，它们的主键都是链接回Person（PersonID）的外键。
我如何在这里创建一个不相交的地方，每个人只能是房客或雇员？谢谢

So I have three tables which I want to create with a disjoint Connection. These are person, Tenant and employee. So every person must either be a Tenant or an employee, never both. I create a Person table with the PersonID primary key, and tenant and Employee as two separate tables whose Primary Keys are both Foreign Keys linking back to the Person(PersonID). How do i create a Disjoint here where every Person can only be either a Tenant or an Employee? Thanks

推荐答案

您可以尝试一下，它会挑选出每个人或一个员工：

You can try this, which will pick out every person who is EITHER a person OR an employee:

SELECT *
FROM (SELECT person.*,
             CASE WHEN employee.id IS NULL THEN 0 ELSE 1 END AS is_employee,
             CASE WHEN tenant.id   IS NULL THEN 0 ELSE 1 END AS is_tenant
      FROM person LEFT JOIN employee on person.id = employee.id
                  LEFT JOIN tenant on person.id = tenant.id) AS tA
WHERE tA.is_employee <> tA.is_person 

确保id列均已索引。

这篇关于如何用不连续的连接在SQL中编写表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！