simple JOIN query 1

[pease]

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:

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.

 

[r937]

change this --

left(t1.pcode,2) AS t1.postcode

to this --

left(t1.pcode,2) AS postcode

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[pease]

I now get a "#1054 - Unknown column 'postcode' in 'where clause'" error.

 

[r937]

oh, yeah, i didn't read past the first error ;-)

SELECT COUNT(thename) AS howmany
     , LEFT(t1.pcode,2) AS postcode
     , t2.town 
  FROM fullsubs AS t1 
INNER
  JOIN towns AS t2 
    ON t2.pcode = left(t1.pcode,2) 
GROUP 
    BY t2.town 
ORDER 
    BY howmany DESC

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[pease]

Thank you