mysql - 使用多个表连接或多个选择编写 MySQL 查询

Question

我正在尝试编写一个 MySQL 查询，该查询为我提供组织名称、其邮政编码、属于该组织的任何事件以及该事件的邮政编码的结果。我尝试了各种连接、连接和选择组合，但均无济于事。这是可能的事吗？ (我可以有一个单独的组织地址和事件地址表，但似乎应该可以只使用一个表)

我的表结构:

mysql> DESCRIBE cc_organisations;
+-------------+------------------+------+-----+---------+----------------+
| Field       | Type             | Null | Key | Default | Extra          |
+-------------+------------------+------+-----+---------+----------------+
| id          | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| user_id     | int(10) unsigned | NO   | MUL | NULL    |                |
| type        | enum('C','O')    | YES  |     | NULL    |                |
| name        | varchar(150)     | NO   | MUL | NULL    |                |
| description | text             | YES  |     | NULL    |                |
+-------------+------------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

mysql> DESCRIBE cc_events;
+-------------+------------------+------+-----+---------+----------------+
| Field       | Type             | Null | Key | Default | Extra          |
+-------------+------------------+------+-----+---------+----------------+
| id          | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| org_id      | int(10) unsigned | NO   | MUL | NULL    |                |
| name        | varchar(150)     | NO   | MUL | NULL    |                |
| start_date  | int(11)          | NO   | MUL | NULL    |                |
| end_date    | int(11)          | YES  | MUL | NULL    |                |
| start_time  | int(11)          | NO   |     | NULL    |                |
| end_time    | int(11)          | NO   |     | NULL    |                |
| description | text             | YES  |     | NULL    |                |
+-------------+------------------+------+-----+---------+----------------+
8 rows in set (0.00 sec)

mysql> DESCRIBE cc_addresses;
+--------------+------------------+------+-----+---------+----------------+
| Field        | Type             | Null | Key | Default | Extra          |
+--------------+------------------+------+-----+---------+----------------+
| id           | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| org_id       | int(10) unsigned | YES  | MUL | NULL    |                |
| event_id     | int(10) unsigned | YES  | MUL | NULL    |                |
| post_code    | varchar(7)       | NO   | MUL | NULL    |                |
| address_1    | varchar(100)     | NO   |     | NULL    |                |
| address_2    | varchar(100)     | YES  |     | NULL    |                |
| town         | varchar(50)      | NO   |     | NULL    |                |
| county       | varchar(50)      | NO   |     | NULL    |                |
| email        | varchar(150)     | NO   |     | NULL    |                |
| phone        | int(11)          | YES  |     | NULL    |                |
| mobile       | int(11)          | YES  |     | NULL    |                |
| website_uri  | varchar(150)     | YES  |     | NULL    |                |
| facebook_uri | varchar(250)     | YES  |     | NULL    |                |
| twitter_uri  | varchar(250)     | YES  |     | NULL    |                |
+--------------+------------------+------+-----+---------+----------------+
14 rows in set (0.00 sec)

Answer 1

select o.Name, oAddress.PostCode, e.Name, eAddress.PostCode 
from cc_organisations o
inner join cc_addresses oAddress on oAddress.org_id = o.id
left outer join cc_events e on e.org_id=o.id
inner join cc_addresses eAddress on eAddress.event_id = e.id