问题简介:
我有一个名为“客户”的超类表和两个从客户继承的名为“个人”和“公司”的子表。因此,客户实体与“人”或“公司”具有一对一的关系。
(客户只能是“个人”或“公司”,但不能同时是两者)
如下所示:
Customer Person Company
+-------+------+------+ +-------+------+------+ +-------+------+------+
| cID| col2| col3| | cID| fname| sname| | cID| name| col3|
+-------+------+------+ +-------+------+------+ +-------+------+------+
|1 |? |? | |1 |JJ |AZ | |4 |ABCD |? |
+-------+------+------+ +-------+------+------+ +-------+------+------+
|2 |? |? | |2 |CC |LL | |5 |BCDE |? |
+-------+------+------+ +-------+------+------+ +-------+------+------+
|3 |? |? | |3 |OO |BB | |6 |CDEF |? |
+-------+------+------+ +-------+------+------+ +-------+------+------+
|4 |? |? | |7 |JK |NN | |8 |DEFG |? |
+-------+------+------+ +-------+------+------+ +-------+------+------+
|5 |? |? | |9 |RR |LW | |... |EFGH |? |
+-------+------+------+ +-------+------+------+ +-------+------+------+
|6 |? |? | |10 |GN |QN | |... |FGHI |? |
+-------+------+------+ +-------+------+------+ +-------+------+------+
|7 |? |? | |... |XC |YU | |... |GHIJ |? |
+-------+------+------+ +-------+------+------+ +-------+------+------+
|8 |? |? |
+-------+------+------+
|9 |? |? |
+-------+------+------+
|10 |? |? |
+-------+------+------+
|... |? |? |
+-------+------+------+
- Person 表将客户的全名存储在单独的名字和姓氏属性中。
- 公司表将公司名称完全存储在一个字段中。
意图与尝试:
我想做的是查询数据库,以便我可以从客户表中选择 ID,加入个人和公司以检索名称属性。
以下是我尝试过的:
SELECT tc."cust_id",
CONCAT(tp."forename", ' ', tp."surname") AS "name",
tcp."name"
FROM "tbl_customer" AS tc
LEFT JOIN "tbl_person" AS tp
ON tc."cust_id" = tp."cust_id"
LEFT JOIN "tbl_company" AS tcp
ON tc."cust_id" = tcp."cust_id"
执行上面的 SQL 会得到以下结果,右边是我想要实现的结果:
Result Result
+-------+------+------+ +-------+------+
| cID| name| name| | cID| name|
+-------+------+------+ +-------+------+
|1 |JJAZ |null | |1 |JJAZ |
+-------+------+------+ +-------+------+
|2 |CCLL |null | |2 |CCLL |
+-------+------+------+ +-------+------+
|3 |OOBB |null | |3 |OOBB |
+-------+------+------+ +-------+------+
|4 |null |ABCD | |4 |ABCD |
+-------+------+------+ +-------+------+
|5 |null |BCDE | |5 |BCDE |
+-------+------+------+ +-------+------+
|6 |null |CDEF | |6 |CDEF |
+-------+------+------+ +-------+------+
|7 |JKNN |null | |7 |JKNN |
+-------+------+------+ +-------+------+
|8 |null |DEFG | |8 |DEFG |
+-------+------+------+ +-------+------+
|9 |RRLW |null | |9 |RRLW |
+-------+------+------+ +-------+------+
|10 |GNQN |null | |10 |GNQN |
+-------+------+------+ +-------+------+
|... |? |? | |... |? |
+-------+------+------+ +-------+------+
描述:
如前所述,我试图将个人和公司的名称合并在同一列下。两个表上的标准 JOIN 将不起作用,因为它将返回 NULL 结果。由于 LEFT JOIN 的性质,预计会出现 NULL 值。这可以通过 SQL UNION 非常简单地解决,我知道解决方案,但是我正在寻找通过 JOIN 来替代 UNION 运算符的方法。
我是否可以执行 JOIN、分组/合并名称列?或者类似的东西?但不是一定要用SQL UNION吗?
Update:
两个答案均来自胡安·卡洛斯·奥罗佩萨 and Becuzz都同样可以接受。