mysql - 2 多对多关系 - 使用 join 提取数据

Question

请检查下面链接中的架构。

http://s11.postimg.org/dydr5ru3n/stack.png

我想选择所有特性以及每个特性的代理人和所有者。问题是代理和所有者的详细信息都来自同一个表，并且存在冲突。

我应该遵循什么解决方案？

注意:不幸的是我无法更改数据库架构。

Answer 1

SELECT oPers.first_name AS ownerFirstName
    , aPers.first_name AS agentFirstName
    ....
FROM properties AS p
LEFT JOIN owners AS po ON p.property_id = po.property_id
LEFT JOIN people AS oPers ON po.person_id = oPers.person_id
LEFT JOIN agents AS pa ON p.property_id = pa.property_id
LEFT JOIN people AS aPers ON pa.person_id = aPers.person_id
;