基于子串匹配关联两表的SQL查询问题及报错排查

原SQL的核心错误 #

1. 语法与类型转换错误：JOIN条件中的SUBSTRING_INDEX(people.buildingIDRoomID, '-', 1) -1)存在两处问题：
   • 多了一个冗余的右括号，属于语法错误；
   • 多余的-1会强制把SUBSTRING_INDEX返回的字符串（如AB、AA）转换为数值，这类字符串无法转成有效数值，触发#1292 Truncated incorrect DOUBLE value: '-'警告。
2. 表引用错误：SELECT子句里写了buildings.*，但JOIN的表是buildinglocations，未给它设置buildings别名，会导致字段引用失败。
3. JOIN类型不合理：使用LEFT JOIN却在WHERE子句中过滤Owner = 'Banta'，这会自动将LEFT JOIN降级为INNER JOIN（不匹配的记录Owner为NULL，会被WHERE条件过滤），直接用INNER JOIN更符合逻辑。

修正后的SQL语句 #

SELECT people.*, buildinglocations.*
FROM `people`
INNER JOIN buildinglocations
  ON SUBSTRING_INDEX(people.BuildingIDRoomID, '-', 1) = buildinglocations.buildingID
WHERE `peopleStatus` = 'N' AND Sender = 'CC' AND Owner = 'Banta'
ORDER BY people.`DateTimeStamp` ASC

说明 #

• 修正后的JOIN条件正确提取BuildingIDRoomID中-左侧的字符串，与buildinglocations.buildingID做字符串匹配，彻底避免类型转换错误；
• 替换LEFT JOIN为INNER JOIN，明确只返回两张表匹配的记录；
• 修正了SELECT子句的表引用错误，确保能正确获取buildinglocations的字段。

执行修正后的语句后，#1292警告会消失，phpMyAdmin与PHP脚本的查询结果也会保持一致。

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