mysql - 复杂的 MySQL 加入 3 个表和 many2many 关系

Question

我有 3 个表 person、account 和 subscription。帐户属于一对多的个人，帐户和订阅是多对多的。我需要一个可以从三个表中选择数据的 sql。-

人员表

+----------------------------+--------------+------+-----+---------+----------------+
| Field                      | Type         | Null | Key | Default | Extra          |
+----------------------------+--------------+------+-----+---------+----------------+
| id                         | int(11)      | NO   | PRI | NULL    | auto_increment |
| username                   | varchar(128) | NO   |     | NULL    |                |
| password                   | varchar(128) | NO   |     | NULL    |                |
| account_id                 | int(11)      | NO   |     | NULL    |                |
+----------------------------+---------------+------+---------------+----------------+

账户表

+--------------+--------------+------+-----+---------+----------------+
| Field        | Type         | Null | Key | Default | Extra          |

+--------------+--------------+------+-----+---------+----------------+
| id           | int(11)      | NO   | PRI | NULL    | auto_increment |
| yearly_renew | tinyint(1)   | NO   |     | NULL    |                |
| date_joined  | date         | NO   |     | NULL    |                |
| last_renewed | datetime     | YES  |     | NULL    |                |
| signup       | tinyint(1)   | NO   |     | NULL    |                |
| reason       | varchar(100) | YES  |     | NULL    |                |
| yearly_total | int(11)      | YES  |     | NULL    |                |
| total        | int(11)      | YES  |     | NULL    |                |
+--------------+--------------+------+-----+---------+----------------+

账户-订阅表

+-----------------+---------+------+-----+---------+----------------+
| Field           | Type    | Null | Key | Default | Extra          |
+-----------------+---------+------+-----+---------+----------------+
| id              | int(11) | NO   | PRI | NULL    | auto_increment |
| account_id      | int(11) | NO   | MUL | NULL    |                |
| subscription_id | int(11) | NO   | MUL | NULL    |                |
+-----------------+---------+------+-----+---------+----------------+

订阅表

+-------------------+--------------+------+-----+---------+----------------+
| Field             | Type         | Null | Key | Default | Extra          |
+-------------------+--------------+------+-----+---------+----------------+
| id                | int(11)      | NO   | PRI | NULL    | auto_increment |
| subscription_type | varchar(150) | YES  |     | NULL    |                |
| hold              | tinyint(1)   | NO   |     | NULL    |                |
| start_date        | date         | YES  |     | NULL    |                |
| end_date          | date         | YES  |     | NULL    |                |
| amount_paid       | double       | YES  |     | NULL    |                |
| date_paid         | date         | YES  |     | NULL    |                |
| transaction_id    | int(11)      | YES  |     | NULL    |                |
| free              | tinyint(1)   | NO   |     | NULL    |                |
+-------------------+--------------+------+-----+---------+----------------+

在单个查询中期望输出 -

输出

+-----------+------------+--------------+
| person.id | account.id | subscription.id |
+-----------+------------+--------------+
|     10    |      11 |           20 |
|     15    |      32 |           45 |
|     23    |      43 |         null |
+--------+---------+-----------------+

Answer 1

试试这个。它可能会正常工作......

SELECT  p.Id AS person.id
       ,p.account_id AS account.id
       ,acsub.subscription_id as subscription.id
FROM   Person AS p
       LEFT JOIN Account-Subscription AS acsub ON p.account_id=acsub.account_id