April 10, 2007 at 8:08 pm
I have 2 tables, one contains a list of all postcodes and their suburbs, the other table contains a list of members, and their postcode address. In the full postcode list, there might be 4 suburbs to a postcode i.e.
tblPostcodes
Postcode Suburb Latitude Longitude
5555 Burb1 44.4444 -39.3933
5555 Burb2 44.4444 -39.3911
5555 Burb3 44.4444 -39.1002
3958 Another Burb 29.2999 -29.2999
tblMembers
Postcode
Member1 5555
Member2 3958
Member3 5555
I need a query which will return the postcode, the count the first suburb name found and the latitude and longitude, i.e.
Postocde Count Suburb Latitude Longitude
5555 2 Burb1 44.4444 -39.3933
3958 1 Another Burb 29.2999 -29.2999
I can return the distinct postcode and count, but once I start with the suburb and lat, long, it starts returning all the postcodes
Thanks in advance
April 10, 2007 at 8:32 pm
SELECT A.PostCode, [Count] = (SELECT COUNT(*) FROM tblMembers Z WHERE Z.Postcode = A.Postcode),
A.Suburb, B.Latitude, B.Longitude
FROM (SELECT Z.Postcode, Suburb = MIN(Z.Suburb) FROM tblPostcodes Z GROUP BY Z.PostCode) A
INNER JOIN tblPostcodes B ON B.Postcode = A.Postcode AND B.Suburb = A.Suburb
April 10, 2007 at 8:39 pm
that's fantastic koji! i'm goign through it now to make heads on tails of how it works!
April 10, 2007 at 8:47 pm
how would i modify this so that i only want the rows returned where the count is > 0??
April 10, 2007 at 11:06 pm
SELECT A.PostCode, A.[Count],
A.Suburb, B.Latitude, B.Longitude
FROM (SELECT Z.Postcode, Suburb = MIN(Z.Suburb),
[Count] = (SELECT COUNT(*) FROM #tblMembers Y WHERE Y.Postcode = Z.Postcode)
FROM #tblPostcodes Z GROUP BY Z.PostCode) A
INNER JOIN #tblPostcodes B ON B.Postcode = A.Postcode AND B.Suburb = A.Suburb
WHERE A.[Count] > 0
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply