找到你要的答案

Q:Mysql: query not giving accurate result with IN clause and inner query

Q:MySQL查询不准确的结果:在条款内查询

I'm trying to get zip codes from zip_id's which are internally stored in companies service table below screens will give you clear idea

I have wrote this query enter image description here

companies service table enter image description here

Please suggest me your valuable views . Thanks in advance.

我试图从zip_id的内部存储在公司服务表屏幕得到邮政编码会给你明确的想法

I have wrote this query enter image description here

companies service table enter image description here

请给我你宝贵的意见。先谢谢了。

answer1: 回答1:

As already mentioned your database scheme is not very well designed, it violates even 1st normal form. You'd need another table where you'd store serv_area_id and zip_code (with possibly multiple rows for a signle serv_area_id) and search within this table and eventually join your original table.

Nevertheless, in order to get the result you describe you cannot use the IN operator as it operates on a value and multiple values in a form of table (either explicit via nested SELECT or enumeration literal (val1, ..., valN)). I would try some string matching as illustrated below. However, consider it rather an ugly hack than correct solution(!)

SELECT zip FROM cities_extended WHERE (
    SELECT GROUP_CONCAT(',', serv_are_zipcodes)
    FROM company_service_areas WHERE ...
) LIKE concat('%(', id, ')%')

如前所述,您的数据库方案设计得不好,它甚至违反了第一种正常格式。你需要另一个表在你店serv_area_id和zip_code(可能有多个行单serv_area_id)和搜索在本表并最终加入你的原始表。

然而,为了得到你描述你不能使用在操作上的操作中的一种形式,表值和多值(无论是明确的通过嵌套的SELECT或枚举文字(val1,…,VALN))。我会尝试一些字符串匹配如下所示。然而,考虑它比正确的解决方案,而不是一个丑陋的黑客!

SELECT zip FROM cities_extended WHERE (
    SELECT GROUP_CONCAT(',', serv_are_zipcodes)
    FROM company_service_areas WHERE ...
) LIKE concat('%(', id, ')%')
mysql