I've developed a postcode search that is used to find candidates in our database. Now, a user can have multiple postcodes associated with them (stored in the users_locations) table. My problem is that when we search for a postcode sometimes the user doesn't come up even though they exist with that postcode associated with them.
For example, a user may have the following 3 postcodes associated with them:
When I search for CM5 and CM6 the user is found. However if I search for CM14 it doesn't get found. Does anyone have an idea why? Is it a GROUP BY problem maybe?
Here is the SQL
SELECT users . * , l.display_name, l.postcode, l.display_county
INNER JOIN (
SELECT user_id, if( count( user_id ) >3, 'Locations throughout the UK', group_concat( display_name
SEPARATOR ' ' ) ) AS display_name, postcode, display_county, location_id
GROUP BY user_id
) AS l ON l.user_id = users.id
WHERE users.status = '1'
AND users.cv_hide = '0'
ORDER BY cv_date DESC
LIMIT 0 , 30