"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"
this Query produces following result as shown in image
https://drive.google.com/file/d/0B3RNacAE6rR5Rk8tUUI2Q3B3X3M/view?usp=sharing
blue marked is problem record that should not come.
Above Query list all records with similiar street address but failed to get only those records with similiar address && city. I need to get only those records which are having same city and streetaddress . is there any way to apply and logic for groupby
Your main select's WHERE clause only checks for a matching address :
AND family.streetAddress in (....)
which will match any address found in your subselect, even if it belongs to the wrong city. The subselect currently only returns the address, but not the matching city. That's the problem.
I'm not sure that this will fix your problem, but you can try to can change the subselect into a nested table expression and match on both address and city. Maybe something along the lines of
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
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments