mysql : using like return duplicate row count

Question

i have an issue on the below

table1

first case :

postcode
LS1 1LS

second case:

postcode
LS11LS

table2

    postcode| region
    LS1 | Leeds
    LS11    | Leeds

and i am using the below query to count the region versus related postcode

SELECT table2.region as 'Region', COUNT( table2.postcode ) as  'count'
FROM table1
INNER JOIN table2 ON table1.postcode LIKE CONCAT( table2.postcode,  '%' ) 
WHERE table1.postcode >  ''
GROUP BY Region

on applying that on table 1 first case i get

Leeds | 1

on applying it on table 1 second case i get

Leeds | 2

for some reason on this postcode / similar postcodes where the other half is exactly like the first part and there is no spaces in between the like inner join returns the count as 2.

any solution for this ?

see this sqlfiddle

I guess I am not following you data example. How would the Leeds entry of `LS2` be joined to either of `LS1 1LS` or `LS11LS`? Do both `LS1 1LS` and `LS11LS` exist in table 1 at the same time? — Mike Brant, Mar 26 '13 at 18:25
@MikeBrant sorry its LS1 , edited , and yes entries may occur on one table by either LS1 1LS or LS11LS by an end user mistake — Ahmed ElGamil, Mar 26 '13 at 18:31
@AhmedElGamil [Cannot repeat](http://sqlfiddle.com/#!2/38121/1). Are you sure you weren't testing case 2 by inserting the second row while keeping the first, which would give 2 matching rows? — Joachim Isaksson, Mar 26 '13 at 18:33
hmm this fiddle is pretty fine, my code is exactly the same as here and it only gives count 2 if i changed this record to LS11LS with no space , i truncated the table and still same .. ill recheck my code — Ahmed ElGamil, Mar 26 '13 at 18:43
@JoachimIsaksson i think i got it , i have 2 records on the table2 referring to the LEEDS region , i have LS1 | LEEDS and another one LS11 | LEEDS so its pretty logical why i have 2 , but any solution for this ? — Ahmed ElGamil, Mar 26 '13 at 18:55

score 0 · Answer 1 · answered Mar 26 '13 at 18:35

0

If you have two entries in table one with values LS1 1LS and LS11LS, your join will match both of these, giving you a count of 2.

answered Mar 26 '13 at 18:35

Mike Brant

70,514
10
99
103

mysql : using like return duplicate row count

1 Answers1