mysql - 如何在sql中的where子句中连接多个列？

Question

如果我要搜索城市地址，例如“Quezon City”，就会有一个结果。但是，如果我搜索城市地址和省份，例如“马尼拉大都会奎松市”，则没有结果。

这是我目前的 sql 查询。

//TABLES
   t1 = street
   t3 = province
   t4 = city
   t5 = village

SELECT t1.*
     , t1.name AS propertyname
     , t2.name AS typeName
     , t3.name AS provName
     , t4.name AS cityName
     , t5.name AS brgyName
     , t6.imgpath_tn AS imgName
FROM proplistings AS t1
     LEFT JOIN proptypes AS t2 ON t2.id = t1.property_type_id
     LEFT JOIN tbl_province AS t3 ON t3.id = t1.prov_id 
     LEFT JOIN tbl_city AS t4 ON t4.id = t1.city_id
     LEFT JOIN tbl_barangay AS t5 ON t5.id = t1.brgy_id
     LEFT JOIN proplistings_images AS t6 ON t6.propid = t1.id
WHERE t1.deleted = 0
     AND t1.announceas = '1'
     AND t6.defaultimg = 1
     AND t6.imgpath != ''
     AND t1.status=1
     AND CONCAT(t1.street,', t5.name,', ', t4.name,', ', t3.name, ',t1.propcode,', ')
         LIKE '%Quezon City, Metro Manila%'
ORDER BY t1.date_added DESC

Answer 1

如果您连接并且任何列包含 null，则结果为 null。尝试为 null 编码。

例如

set @a = 'abc';
set @b = null;

select @a,@b,concat(@a,@b),
        concat(ifnull(@a,''),ifnull(@b,''));

+------+------+---------------+-------------------------------------+
| @a   | @b   | concat(@a,@b) | concat(ifnull(@a,''),ifnull(@b,'')) |
+------+------+---------------+-------------------------------------+
| abc  | NULL | NULL          | abc                                 |
+------+------+---------------+-------------------------------------+
1 row in set (0.00 sec)