Using MYSQL Associative Table and JOIN

Question

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.

Answer 1

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``

Answer 2

Your data structure is correct, and using a mapping table between the addresses and contacts tables is a great approach. My only comment would be that contact_id and address_id might be a more suitable column name than idcontact and idaddress, but that's up to you and it works fine the way it is.

You can use joins to achieve this relationship. Left join will return the contact even if it doesn't match any of the other table records, or you could use an inner join to only return it if it finds a match in each table.

SELECT
    C.*,
    A.*
FROM contacts C
LEFT JOIN contacts_addresses CA
    ON CA.idcontact = C.id
LEFT JOIN address A
    ON CA.idaddress = A.id

Answer 3

SELECT C.id, C.name, A.street, A.city, A.state, A.zip
FROM contacts_addresses CA
INNER JOIN contacts C ON C.id = CA.idcontact
INNER JOIN addresses A ON A.id = CA.idaddress;

SQL Fiddle

Answer 4

Yet another example. It appears that two people are roommates?

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

fiddle

Answer 5

You need to use inner join to solve your problem, the appropriate query for this will be

SELECT con.name, addr.street, addr.state, addr.zip
from contacts_addresses
inner join contacts con
on con.id = contacts_addresses.idcontact
inner join addresses addr
on addr.id = contacts_addresses.idaddress