mysql - 一个简单的MYSQL View ，用于显示有关玩家及其监护人的信息

我需要创建一个名为phone list的视图，其中列出每个玩家、他们的监护人、监护人的电话号码以及他们所效力的球队。我该如何编码。你需要的所有信息都应该在下面找到：
这是我的桌子：

CREATE TABLE Person
(
personID INT NOT NULL,
name VARCHAR(50),
address VARCHAR(70),
phone VARCHAR(15),
email VARCHAR(30),
year INT,
PRIMARY KEY (personID)
);

CREATE TABLE Player
(
personID INT NOT NULL,
dateOfBirth DATE,
school VARCHAR(30),
PRIMARY KEY (personID),
FOREIGN KEY (personID) REFERENCES Person (personID)
);

CREATE TABLE Team
(
teamID INT NOT NULL,
tName VARCHAR(70),
ageRange VARCHAR(30),
PRIMARY KEY (teamID)
);

CREATE TABLE Coach
(
personID INT NOT NULL,
dateBeganCoaching DATE,
PRIMARY KEY (personID),
FOREIGN KEY (personID) REFERENCES Person (personID)
);

CREATE TABLE coachTeam
(
personID INT NOT NULL,
teamID INT NOT NULL,
PRIMARY KEY (personID, teamID),
FOREIGN KEY (personID) REFERENCES Coach (personID),
FOREIGN KEY (teamID) REFERENCES Team (teamID)
);

CREATE TABLE playerTeam
(
personID INT NOT NULL,
teamID INT NOT NULL,
PRIMARY KEY (personID, teamID),
FOREIGN KEY (personID) REFERENCES Player (personID),
FOREIGN KEY (teamID) REFERENCES Team (teamID)
);

CREATE TABLE Guardian
(
parentID INT NOT NULL,
childID INT NOT NULL,
PRIMARY KEY (parentID, childID),
FOREIGN KEY (parentID) REFERENCES Person (personID),
FOREIGN KEY (childID) REFERENCES Person (personID)
);

CREATE TABLE Qualification
(
qualificationID INT NOT NULL,
qName VARCHAR(30),
PRIMARY KEY (qualificationID)
);

CREATE TABLE coachQualification
(
qualificationID INT NOT NULL,
personID INT NOT NULL,
PRIMARY KEY (personID, qualificationID),
FOREIGN KEY (qualificationID) REFERENCES Qualification (qualificationID),
FOREIGN KEY (personID) REFERENCES Coach (personID)
);

我该如何做才能使我只使用person表来引用播放器和守护者，我该如何做才能使如果一个播放器有多个守护者，两个守护者将放在同一行，而不是在输出中创建一个完整的新行来显示两个守护者谢谢。
我尝试了以下操作，但出现了错误：

CREATE VIEW phonelist AS
SELECT Child.name, tName, Parent.name, Parent.phone
FROM playerTeam INNER JOIN Person AS Child ON playerTeam.personID = Child.personID
                INNER JOIN Team ON playerTeam.teamID = Team.teamID
                INNER JOIN Person AS Parent ON Guardian.parentID = Parent.personID;

Select * FROM phonelist ORDER BY name ASC;

最佳答案

Select P.ID, P.Name, P2.PersonID, P2.Name, P2.Phone, T2.tName as GuardianTeam T.tName as PersonTeam
INNER JOIN Player PL on PL.PErsonID=P.PersonID
INNER JOIN Guardian G on G.ParentID = P.PersonID
LEFT JOIN Person P2 on P2.PersonID = G.ChildID
INNER JOIN PlayerTeam PT on PT.PersonID = PL.PersonID
LEFT JOIN Player PL2 on PL2.PersonID = P2.PersonID
LEFT JOIN PlayerTeam PT2 on PT2.PersonID = PT2.PersonID
LEFT JOIN Team T2 on T2.TeamID = PT.TeamID
INNER JOIN Team T on T.TeamID = PT.TeamID

啊好的编辑删除个人团队。。。
返回所有玩家、其监护人姓名、电话和监护人团队以及没有监护人的任何玩家；或监护人不属于团队或玩家本身的情况。

Select P.ID, P.Name, P2.PersonID, P2.Name, P2.Phone, T2.tName as GuardianTeam
INNER JOIN Player PL on PL.PErsonID=P.PersonID
INNER JOIN Guardian G on G.ParentID = P.PersonID
LEFT JOIN Person P2 on P2.PersonID = G.ChildID
LEFT JOIN Player PL2 on PL2.PersonID = P2.PersonID
LEFT JOIN PlayerTeam PT2 on PT2.PersonID = PT2.PersonID
LEFT JOIN Team T2 on T2.TeamID = PT.TeamID