Natural Join of two tables in MySQL

Question

I've been looking over this code for the better part of the last hour trying to figure out my misstep. It's probably glaringly obvious, but after so long in front of the computer, I may just need a second set of eyes to have it pop out. I have multiple tables connected to keep them 1NF and 2NF, but there are two tripping me up.

I have a main table, called my_contacts, and one holding the state/city information, called zip_code. zip_code holds the primary key row 'zip_code' with my_contacts holding its foreign key.

Problem is, when I try to join them I get nothin'. Did I screw up somewhere? Below are the SHOW CREATE TABLEs for each:

For my_contacts:

    CREATE TABLE `my_contacts` (
    `contact_id` int(11) NOT NULL AUTO_INCREMENT,
    `last_name` varchar(100) DEFAULT NULL,
    `first_name` varchar(100) DEFAULT NULL,
    `phone` varchar(13) DEFAULT NULL,
    `email` varchar(100) DEFAULT NULL,
    `gender` char(1) DEFAULT NULL,
    `birthday` date DEFAULT NULL,
    `prof_id` int(11) DEFAULT NULL,
    `zip_code` int(11) DEFAULT NULL,
    PRIMARY KEY (`contact_id`),
    KEY `mc_profid_fk` (`prof_id`),
    KEY `my_zip_fk` (`zip_code`),
    CONSTRAINT `mc_profid_fk` FOREIGN KEY (`prof_id`) 
    REFERENCES `profession` (`prof_id`),
    CONSTRAINT `my_zip_fk` FOREIGN KEY (`zip_code`) REFERENCES `zip_code` (`zip_code`)
    ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8

for zip_code:

    CREATE TABLE `zip_code` (
    `zip_code` int(11) NOT NULL AUTO_INCREMENT,
    `city` varchar(50) DEFAULT NULL,
    `state` char(2) DEFAULT NULL,
    PRIMARY KEY (`zip_code`)
    ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8

Thanks in advance.

Answer 1

The zip_code in the zip_code table is set to auto_increment.

This doesn't seem like a likely choice for this field in that table. Where do you actually store the zip_code? By the way, I would expect the actual zip code to be stored as a character string, to handle leading 0s. A zip code may look like a number, but it really isn't one (ordering is not very well defined, and arithmetic operations make no sense).