HI,
I am attempting a straight forward join but cant seem to get it to work.
Two tables (fullsubs) and (towns) the fullsubs table holds among other things the UK postcode of the member. Table two contains the prefixes of postcodes (2 chars) and the correspoding town/city.
I want to get a count of how many members are in each city so I need to look at the first two chars of the postcode and cross compare that to the towns.
eg NE3 5AA would compare to the towns table to find NE - Newcastle upon Tyne.
it may be worth noting that in some cases there is more than one entry for a town, Newcastle has NE as its prefix however Birmingham has B1, B2, B3 et through to B9 as its prefixes.
Heres what I have:
But I receive a #1064 error. Ive tried numerous things and am guessing its down to the left(pcode,2) bit which Ive tried with and without the t1. portion.
I am attempting a straight forward join but cant seem to get it to work.
Two tables (fullsubs) and (towns) the fullsubs table holds among other things the UK postcode of the member. Table two contains the prefixes of postcodes (2 chars) and the correspoding town/city.
I want to get a count of how many members are in each city so I need to look at the first two chars of the postcode and cross compare that to the towns.
eg NE3 5AA would compare to the towns table to find NE - Newcastle upon Tyne.
it may be worth noting that in some cases there is more than one entry for a town, Newcastle has NE as its prefix however Birmingham has B1, B2, B3 et through to B9 as its prefixes.
Heres what I have:
Code:
SELECT count(thename) AS howmany, left(t1.pcode,2) AS t1.postcode, t2.town from fullsubs AS t1 JOIN towns AS t2 WHERE (t1.postcode = t2.pcode) GROUP BY town ORDER BY howmany DESC
But I receive a #1064 error. Ive tried numerous things and am guessing its down to the left(pcode,2) bit which Ive tried with and without the t1. portion.