mysql - 从已选择的表中选择数据

Question

我有一些表正在尝试从中获取数据。我需要将 cust 和 emp 表链接到 sales 表。然后，依次从 person 表中获取 cust 和 emp 的姓名。

我被困住了

数据如下:

表销售

+--------+--------+-------+-------+
| saleID | custID | empID | total |
|--------+--------+-------+-------|
|    1   |    1   |   1   |  3.00 |
|--------+--------+-------+-------|
|    2   |    2   |   3   |  6.00 |
|--------+--------+-------+-------|
|    3   |    3   |   1   |  9.00 |
|--------+--------+-------+-------|
|    4   |    2   |   2   |  2.00 |
|--------+--------+-------+-------|
|    5   |    3   |   3   |  1.00 |
|--------+--------+-------+-------+

表客户

+--------+---------+----------+
| custID | company | personID
+--------+---------+----------+
|    1   |  comp1  +    2     |
+--------+---------+----------+
|    2   |  comp2  +    4     |
+--------+---------+----------+
|    3   |  comp3  +    6     |
+--------+---------+----------+

表emp

+--------+----------+----------+
| custID | username | personID |
+--------+----------+----------+
|    1   |   emp1   |    1     |
+--------+----------+----------+
|    2   |   emp2   |    3     |
+--------+----------+----------+
|    3   |   emp3   |    5     |
+--------+----------+----------+

表人

+----------+------+
| personID | name |
+----------+------+
|     1    | per1 |
+----------+------+
|     2    | per2 |
+----------+------+
|     3    | per3 |
+----------+------+
|     4    | per4 |
+----------+------+
|     5    | per5 |
+----------+------+
|     6    | per6 |
+----------+------+

我想查询给我这个:

+--------+--------+----------+-------+---------+-------+
| saleID | custID | custName | empID | empName | total |
+--------+--------+----------+-------+---------+-------+
|    1   |    1   |   per2   |   1   |   per1  | 3.00  |
+--------+--------+----------+-------+---------+-------+
|    2   |    2   |   per4   |   3   |   per5  | 6.00  |
+--------+--------+----------+-------+---------+-------+
|    3   |    3   |   per6   |   1   |   per1  | 9.00  |
+--------+--------+----------+-------+---------+-------+
|    4   |    2   |   per4   |   2   |   per3  | 2.00  |
+--------+--------+----------+-------+---------+-------+
|    5   |    3   |   per6   |   3   |   per5  | 1.00  |
+--------+--------+----------+-------+---------+-------+

Answer 1

简单的 JOIN 就可以解决问题:

SELECT 
    a.saleID,
    b.custID,
    p1.name as custName,
    c.empID,
    p2.name as empName,
    a.total
FROM SALES a
JOIN cust b
ON a.custID = b.custID
JOIN emp c
ON c.custID = a.custID AND c.empID = a.empID
JOIN person p1
ON p1.personID = b.personID
JOIN person p2
ON p2.personID = c.personID
ORDER BY saleID