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:
CM5
CM6
CM14
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
FROM users
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
FROM users_locations
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
I think I managed to figure it out. Here’s the final SQL:
SELECT users . * , l.display_name, l.postcode, l.display_county
FROM users
INNER JOIN (
SELECT user_id, if( count( user_id ) >3, 'Locations throughout the UK', group_concat( display_name
SEPARATOR ' ' ) ) AS display_name, group_concat( postcode
SEPARATOR ' ' ) AS postcode, display_county, location_id
FROM users_locations
GROUP BY user_id
) AS l ON l.user_id = users.id
WHERE users.status = '1'
AND users.cv_hide = '0'
AND (
l.postcode LIKE '%CM14%'
)
ORDER BY cv_date DESC
LIMIT 0 , 30
