mysql - 使用 MYSQL 关联表和 JOIN

Question

三个表的结构如下:

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;

示例数据:

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

我正在尝试运行一个查询，它将让我指定一个唯一的联系人 ID，并提取他们的关联地址。几天来我一直在努力解决这个问题，但我只是不明白连接是如何工作的。其他论坛、文章、 Material 并没有帮助我阐明这个特定问题。

我是否正确地构建了表格？我应该在某处使用外键吗？我是否为关联表/列使用了适当的命名约定？

感谢任何帮助，无论是解决方案还是显示查询结构的伪代码 - 谢谢。

Answer 1

要获取一个特定联系人的所有地址，请说 concatid 3 你可以这样做

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 

要获取所有联系人，只需删除最后一个 where 条件``