[SOLVED] Using MYSQL Associative Table and JOIN

Using MYSQL Associative Table and JOIN

Here is the structure of the three tables:

CREATE TABLE `contacts` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(99) DEFAULT NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;

CREATE TABLE `addresses` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `street` varchar(99) DEFAULT NOT NULL,
  `city` varchar(99) DEFAULT NOT NULL,
  `state` varchar(20) DEFAULT NOT NULL,
  `zip` int(9) DEFAULT NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;

CREATE TABLE `contacts_addresses` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `idcontact` int(9) DEFAULT NOT NULL,
  `idaddress` int(9) DEFAULT NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;

Sample data:

mysql> select * from contacts;
+----+----------------+
| id | name           |
+----+----------------+
|  1 | hank kingsley  |
|  2 | phil collins   |
|  3 | sam weisgamgee |
|  4 | john johnson   |
|  5 | dale girdley   |
+----+----------------+

mysql> SELECT * FROM addresses;
+----+--------------------+-----------+-------+-------+
| id | street             | city      | state | zip   |
+----+--------------------+-----------+-------+-------+
|  1 | rainbow lane       | fairytown | VT    | 52689 |
|  2 | townie ave         | manhattan | NY    | 98569 |
|  3 | sayitain'tso drive | oldsville | KY    | 25689 |
|  4 | somehow circle     | Anytown   | TX    | 84757 |
+----+--------------------+-----------+-------+-------+

mysql> select * from contacts_addresses;
+----+-----------+-----------+
| id | idcontact | idaddress |
+----+-----------+-----------+
|  1 |         3 |         1 |
|  2 |         3 |         2 |
|  3 |         5 |         3 |
|  4 |         1 |         1 |
|  5 |         4 |         2 |
+----+-----------+-----------+

I am trying to run a query which will let me specify a unique contact's id, and pull their associated addresses. I've been trying to figure this out for a couple of days, but I just don't understand how joins work. Other forums, articles, material haven't helped me illuminate this particular issue.

Am I structuring the tables correctly? Should I be using foreign keys somewhere? Am I using an appropriate naming convention for the associative table/columns?

Any help is appreciated, either a solution or pseudo-code to show the structure of the query - thank you.

Solution

For getting all the address for one particular contact say concatid 3 you can do something as

select 
c.id,
c.name,
a.street,
a.city,
a.zip,
a.state
from contacts_addresses ca
join contacts c on c.id = ca.idcontact
join addresses a on a.id = ca.idaddress
where c.id = 3

To get for all the contacts just remove the last where condition``