带STATE_CODE筛选的三表SQL连接查询需求及问题解决

你之前的核心问题是未关联CITY表——STATE_CODE字段仅存在于CITY表中，不关联该表就无法基于州代码做有效筛选。同时要保留无活动的站点，必须用左连接关联ACTIVITIES，确保符合条件的站点不会被过滤。

正确SQL语句 #

SELECT
    a.ACTIVITY_NAME AS ACTIVITY,
    s.SITE_NAME AS SITE,
    c.CITY_NAME,
    c.STATE_CODE
FROM SITE s
-- 关联城市表以获取州代码
LEFT JOIN CITY c ON s.CITY_ID = c.CITY_ID
-- 左连接活动表，保留无对应活动的站点
LEFT JOIN ACTIVITIES a ON s.SITE_ID = a.SITE_ID
-- 替换为目标州代码，比如南达科他州用'SD'
WHERE c.STATE_CODE = 'SD'
ORDER BY s.SITE_NAME, a.ACTIVITY_NAME;

语句说明 #

1. 关联CITY表：通过SITE.CITY_ID与CITY.CITY_ID关联，才能拿到STATE_CODE作为筛选条件。
2. 左连接ACTIVITIES：使用LEFT JOIN而非内连接，保证即使站点没有对应活动，也会被保留（此时ACTIVITY_NAME为NULL）。
3. 州代码筛选：直接通过WHERE c.STATE_CODE指定目标州，仅返回该州下的站点及活动。

如果存在未关联任何城市（SITE.CITY_ID为NULL）的站点，且业务需要将其纳入结果，可修改WHERE条件：

WHERE c.STATE_CODE = 'SD' OR s.CITY_ID IS NULL

匹配预期格式的输出 #

若只需展示ACTIVITY - SITE的格式，可调整SELECT语句：

SELECT
    CONCAT(COALESCE(a.ACTIVITY_NAME, 'NULL'), ' - ', s.SITE_NAME) AS `ACTIVITY - SITE`
FROM SITE s
LEFT JOIN CITY c ON s.CITY_ID = c.CITY_ID
LEFT JOIN ACTIVITIES a ON s.SITE_ID = a.SITE_ID
WHERE c.STATE_CODE = 'SD'
ORDER BY s.SITE_NAME, a.ACTIVITY_NAME;

执行后即可得到与示例一致的输出样式。

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