2

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.

tlaffoon
  • 65
  • 2
  • 10

5 Answers5

4

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

Abhik Chakraborty
  • 44,654
  • 6
  • 52
  • 63
  • That syntax doesn't appear to work, nor am I able to modify it to a working state: `SELECT c.id, c.name, a.street, a.city, a.zip, a.state from contacts_addresses as ca join contacts as c on c.id = ca.idcontact join addresses as a on a.id = ca.idaddress;` – tlaffoon Jun 27 '14 at 04:17
  • I had a bunch of noise in there from previous query attempts, my bad. – tlaffoon Jun 27 '14 at 04:20
  • syntax error? could you show the query which shows syntax error, I suppose the `where c.id = {some concat id}` its just an example in reality it should be say for contact id 2 `where c.id = 2` – Abhik Chakraborty Jun 27 '14 at 04:20
  • This one works beautifully for exactly what I'm trying to do, thank you. I have made slight modifications: `SELECT c.name AS contact_name, CONCAT(a.street, ' ', a.city, ', ', a.zip, ' ', a.state) AS full_address FROM contacts_addresses ca JOIN contacts c on c.id = ca.contact_id JOIN addresses a on a.id = ca.address_id WHERE c.id = 3;` – tlaffoon Jun 27 '14 at 04:37
2
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

Sadikhasan
  • 18,365
  • 21
  • 80
  • 122
0

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
scrowler
  • 24,273
  • 9
  • 60
  • 92
  • Agreed contact_id - address_id more intuitive. – tlaffoon Jun 27 '14 at 04:30
  • Regarding my join types comment, you could inner join the contacts_addresses to the addresses as this should be created after the addresses record and should always match, and you could left join the contacts_addresses from contacts because a contact may not always have an address. – scrowler Jun 27 '14 at 04:31
0

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

Fabricator
  • 12,722
  • 2
  • 27
  • 40
  • Correct, I want to be able to work with the data even if addresses are shared amongst contacts. Trying your query out now. – tlaffoon Jun 27 '14 at 04:23
0

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
Prafful Garg
  • 214
  • 1
  • 5