Retrieve Data from Database and Count the number of times the data occurs

I have a couple of tables in M开发者_Go百科ySQL that I'm working with that do not have any relation. They do have a column of similar data (Postal Codes / Zip Codes).

What I have to do with these tables is compare the postal codes from one table compare them to the postal codes from the first table and count them.

For Example.

Table A has a postal code of T0A and T0B (I use only the first three characters in the postal code as this is all I need to compare against)

Table B has 13 rows where the postal code matches T0A and 3 rows where the postal code matches T0B.

So the outcome would look like:

T0A = 13
T0B = 3

HOWEVER, then I need to take these and separate them by city, so since both T0A and T0B could be one city I would need to take those and add them together and get something like.

Edmonton = 16

I've been doing this with for loops and arrays. So I'm reading the data from table A into one array and the data from table b into another array. Then I compare the postal codes from table B to the postal codes in table A using nested for loops in order to count the number of occurrences of the postal codes and then I store them in another array. This is all fine and dandy however now I'm a bit stuck trying to separate the counts into their correct cities and I'm sitting here thinking there must be an easier way to do this. Does anyone have any suggestions, am I going about this all wrong?

Structure - Table A

jos_postalzip_redirect | CREATE TABLE `jos_postalzip_redirect` (
  `id` int(11) NOT NULL auto_increment,
  `country_code` varchar(2) NOT NULL,
  `prov_state_code` varchar(2) NOT NULL,
  `city` varchar(60) NOT NULL,
  `postal_zip` varchar(6) NOT NULL,
  `email_address` varchar(60) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=739 DEFAULT CHARSET=utf8 |

Structure - Table B

jos_form_submitteddata_form1 | CREATE TABLE `jos_form_submitteddata_form1` (
  `id` int(11) NOT NULL auto_increment,
  `bf_status` varchar(20) collate utf8_bin NOT NULL,
  `bf_user_id` int(11) NOT NULL,
  `FIELD_1` varchar(255) collate utf8_bin NOT NULL,
  `FIELD_2` varchar(255) collate utf8_bin NOT NULL,
  `FIELD_3` varchar(255) collate utf8_bin NOT NULL,
  `FIELD_4` varchar(255) collate utf8_bin NOT NULL,
  `FIELD_5` varchar(255) collate utf8_bin NOT NULL,
  `FIELD_6` varchar(255) collate utf8_bin NOT NULL,
  `FIELD_7` varchar(255) collate utf8_bin NOT NULL,
  `FIELD_8` varchar(255) collate utf8_bin NOT NULL,
  `FIELD_23` varchar(255) collate utf8_bin NOT NULL,
  `FIELD_24` varchar(255) collate utf8_bin NOT NULL, //THIS IS THE POSTAL CODE FIELD
  `FIELD_28` varchar(255) collate utf8_bin NOT NULL,
  `FIELD_29` varchar(255) collate utf8_bin NOT NULL,
  `FIELD_30` varchar(255) collate utf8_bin NOT NULL, 
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=4044 DEFAULT CHARSET=utf8 COLLATE=utf8_bin 

---

Just an abstraction of what I understood. You may need to adjust it according to your needs.

In this example I will assume that FIELD_1 in table B is a postal code.

Count by postal code:

select 
    left(ta.postal_zip, 3) p_code, count(*)
from 
    jos_form_submitteddata_form1 tb
    join jos_postalzip_redirect ta on left(tb.field_1, 3) = left(ta.postal_zip, 3)
group by
    p_code

Count by city:

select
    ta.city, count(*)
from
    jos_form_submitteddata_form1 tb
    join jos_postalzip_redirect ta on left(tb.field_1, 3) = left(ta.postal_zip, 3)
group by 
    ta.city