php - 根据三列(city+streetaddress+state)值选择相似地址记录连同mysql中的join操作

Question

“SELECT family.* ,charity.charityName FROM family join charity WHERE charity.user_id = family.createdby AND family.streetAddress in (SELECT family.streetAddress FROM family GROUP BY family.city,family.streetAddress HAVING count (*) > 1 AND ) ORDER BY family.streetAddress ASC LIMIT $offset,$limit"

此查询产生如下结果，如图所示

https://drive.google.com/file/d/0B3RNacAE6rR5Rk8tUUI2Q3B3X3M/view?usp=sharing

蓝色标记的是不应该出现的问题记录。

上面的查询列出了所有具有相似街道地址的记录，但未能仅获取那些具有相似地址 && 城市的记录。我只需要获取那些具有相同城市和街道地址的记录。有什么方法可以为 groupby 应用和逻辑

Answer 1

您的主要选择的 WHERE 子句仅检查匹配地址:

AND family.streetAddress in (....)

这将匹配在您的子选择中找到的任何地址，即使它属于错误的城市。 subselect 目前只返回地址，不返回匹配的城市。这就是问题所在。

我不确定这是否会解决您的问题，但您可以尝试将子选择更改为嵌套表表达式并匹配地址和城市。也许类似于

SELECT family.* --and other main select stuff
WHERE charity.user_id = family.createdby 
AND exists --similar to count(*) > 1

select streetAddress, city 
from (
    (SELECT family.streetAddress, family.city 
        FROM family 
        GROUP BY family.city,family.streetAddress 
        HAVING count(*) > 1) AS subSelect
) 
WHERE family.streetAddress = subSelect.streetAddress 
    AND family.city = subSelect.city