MySQL INNER JOIN... filter repeats and mismatches... postcode JOIN

Answer 1

By using the wildcard "%", its finding anything that matches, much like that of doing a DOS search by file names... CM1 is same wildcard as CM16, CM17, CM192, CM11189. Your join should be on exact same postcode, and only use your wildcard on the result criteria you want to restrict down to...

SELECT 
      user.postcode, 
      postcode.postcode 
   FROM user 
      INNER JOIN postcode ON user.postcode = postcode.postcode
   WHERE 
      user.postcode LIKE  "CM%"
   LIMIT 0 , 30 

Answer 2

try this out

SELECT user.postcode, max(postcode.postcode) as postcode
FROM user
INNER JOIN postcode ON user.postcode LIKE CONCAT( postcode.postcode,  "%" ) 
WHERE user.postcode LIKE  "CM%"
GROUP BY user.postcode
LIMIT 0 , 30

Answer 3

Try matching against the first half of the postcode and a space, eg. LIKE CONCAT(postcode, ' %').

Or this might help you to reformat the post codes:

SELECT
    CONCAT( substr(p, 1, length(p)-3), ' ', substr(p, -3) )
FROM (SELECT 'CM159ES' p) a;