如何创建可识别住宿ID对应类型的SQL多表关联视图

你之前编写的多表关联视图执行失败，存在两类核心问题：

• 语法错误：单条SELECT语句仅支持一个WHERE关键字，且WHERE子句内的多条件需要用逻辑运算符拼接，不能用逗号分隔。你的失败代码不仅重复写了WHERE，第一个关联条件末尾还多了冗余逗号，直接触发语法报错。
• 逻辑错误：直接用逗号分隔多表的写法默认是内连接逻辑，会要求返回的记录同时满足所有表的关联匹配规则。但业务上一个住宿ID只会属于Hotel、Motel、RentalHome三类中的一种，不可能同时在两张及以上类型表中存在对应记录，内连接逻辑最终会过滤掉所有有效住宿数据，返回空结果。

使用左连接（LEFT JOIN）关联三张住宿类型表，通过CASE语句判断当前住宿ID匹配到的类型，即可实现需求：

CREATE VIEW IdentifyAccom AS
SELECT 
    a.accom_id AS AccomID,
    CASE
        WHEN h.accom_id IS NOT NULL THEN 'Hotel'
        WHEN m.accom_id IS NOT NULL THEN 'Motel'
        WHEN rh.accom_id IS NOT NULL THEN 'RentalHome'
        ELSE '未知类型'
    END AS AccomType,
    h.hotel_name AS Hotel_Name,
    m.motel_name AS Motel_Name,
    rh.rental_home_name AS RentalHome_Name
FROM Accommodation a
LEFT JOIN Hotel h ON a.accom_id = h.accom_id
LEFT JOIN Motel m ON a.accom_id = m.accom_id
LEFT JOIN RentalHome rh ON a.accom_id = rh.accom_id;

简化版本（可选） #

如果不需要单独保留三类住宿的名称字段，想直接返回当前住宿对应的统一名称，可以用COALESCE函数取第一个非空的名称值，视图结构更简洁：

CREATE VIEW IdentifyAccom AS
SELECT 
    a.accom_id AS AccomID,
    CASE
        WHEN h.accom_id IS NOT NULL THEN 'Hotel'
        WHEN m.accom_id IS NOT NULL THEN 'Motel'
        WHEN rh.accom_id IS NOT NULL THEN 'RentalHome'
        ELSE '未知类型'
    END AS AccomType,
    COALESCE(h.hotel_name, m.motel_name, rh.rental_home_name) AS AccomName
FROM Accommodation a
LEFT JOIN Hotel h ON a.accom_id = h.accom_id
LEFT JOIN Motel m ON a.accom_id = m.accom_id
LEFT JOIN RentalHome rh ON a.accom_id = rh.accom_id;

上述视图会返回Accommodation表中所有住宿记录：匹配到对应类型表时，AccomType字段会直接标注住宿类型，对应类型的名称字段会返回有效值，其余两类的名称字段为NULL。

内容的提问来源于stack exchange，提问作者Hamza