How to design a "view" in MySQL that will get the results I am looking for?

Question

I've managed to create a view from some data in my MySQL database, as below:

mysql> CREATE VIEW phonelist AS
    -> SELECT parent.parentID AS ParentID,
    -> ParentPerson.firstName AS ParentFirstName,
    -> ParentPerson.lastName AS  ParentLastName,
    -> ChildPerson.firstName AS PlayerFirstName,
    -> ChildPerson.lastName AS  PlayerLastName,
    -> GuardianHomePhone.homeNumber AS GuardianHomePhone
    -> FROM parent
    -> JOIN player ON (parent.parentID IN (player.motherID, player.fatherID))
    -> JOIN person AS ParentPerson ON (ParentPerson.personID = parent.parentID)
    -> JOIN person AS  ChildPerson ON (ChildPerson.personID = player.playerID)
    -> JOIN addressDetails AS GuardianHomePhone ON (GuardianHomePhone.personID = parent.parentID);

Query OK, 0 rows affected (0.00 sec)

mysql> select * from phonelist;

+----------+-----------------+----------------+-----------------+----------------+-------------------+
| ParentID | ParentFirstName | ParentLastName | PlayerFirstName | PlayerLastName | GuardianHomePhone |
+----------+-----------------+----------------+-----------------+----------------+-------------------+
|        8 | Gregory         | Peck           | Michael         | Peck           | 034871234         |
|        9 | Laura           | Peck           | Michael         | Peck           | 034871234         |
|       10 | Martha          | Petersen       | Matt            | Petersen       | 034724321         |
|       10 | Martha          | Petersen       | Christopher     | Petersen       | 034724321         |
|       11 | Chris           | Michaels       | Richard         | Michaels       | 034791212         |
|       11 | Chris           | Michaels       | Shaun           | Michaels       | 034791212         |
|       12 | Nadine          | Michaels       | Richard         | Michaels       | 034791212         |
|       12 | Nadine          | Michaels       | Shaun           | Michaels       | 034791212         |
|       13 | Barry           | Dackers        | Harry           | Dackers        | 034871996         |
|       14 | Kevin           | Mitchell       | Daniel          | Mitchell       | 034742886         |
|       15 | Rebecca         | Mitchell       | Daniel          | Mitchell       | 034742886         |
+----------+-----------------+----------------+-----------------+----------------+-------------------+
11 rows in set (0.00 sec)

mysql>

Creating this view was a challenge, but the description below is what I am having trouble navigating around:

I also need to add the team each player is in with this view. Because getting the team's name to match up requires joining 4 tables together, I am having trouble merging things together. I managed to create a separate query that matches players to teams below:

mysql> select person.firstName, person.lastName, team.teamName
    -> from person join player on person.personID = player.playerID
    -> join teamAllocation on person.personID = teamAllocation.playerID
    -> join team on teamAllocation.teamID = team.teamID;

+-------------+----------+------------+
| firstName   | lastName | teamName   |
+-------------+----------+------------+
| Michael     | Peck     | U10 Red    |
| Christopher | Petersen | U10 Red    |
| Richard     | Michaels | U11 Orange |
| Shaun       | Michaels | U9 Yellow  |
| Matt        | Petersen | U11 Orange |
| Harry       | Dackers  | U9 Yellow  |
| Daniel      | Mitchell | U9 Yellow  |
+-------------+----------+------------+
7 rows in set (0.00 sec)

mysql>

The outcome I am visualizing in my head is something like this:

+----------+-----------------+----------------+-----------------+----------------+----------------+-------------------+
| ParentID | ParentFirstName | ParentLastName | PlayerFirstName | TeamName       | PlayerLastName | GuardianHomePhone |
+----------+-----------------+----------------+-----------------+----------------+----------------+-------------------+
|        8 | Gregory         | Peck           | Michael         | U10 Red        | Peck           | 034871234         |
|        9 | Laura           | Peck           | Michael         | U10 Red        | Peck           | 034871234         |
|       10 | Martha          | Petersen       | Matt            | U11 Orange     | Petersen       | 034724321         |
|       10 | Martha          | Petersen       | Christopher     | U10 Red        | Petersen       | 034724321         |
|       11 | Chris           | Michaels       | Richard         | U11 Orange     | Michaels       | 034791212         |
|       11 | Chris           | Michaels       | Shaun           | U9 Yellow      | Michaels       | 034791212         |
|       12 | Nadine          | Michaels       | Richard         | U11 Orange     | Michaels       | 034791212         |
|       12 | Nadine          | Michaels       | Shaun           | U9 Yellow      | Michaels       | 034791212         |
|       13 | Barry           | Dackers        | Harry           | U9 Yellow      | Dackers        | 034871996         |
|       14 | Kevin           | Mitchell       | Daniel          | U9 Yellow      | Mitchell       | 034742886         |
|       15 | Rebecca         | Mitchell       | Daniel          | U9 Yellow      | Mitchell       | 034742886         |
+----------+-----------------+----------------+-----------------+----------------+----------------+-------------------+

If anyone can help me on this I would be very grateful. For those of you that want to test queries with the data I have put the schema (DDL) and data (DML)scripts (into one "paste") at http://pastebin.com/S4iJyJUh.

Also, if anyone thinks there is a better way I can do the view, let me know.

Answer 1

You're almost there, you just need to combine the two queries.

CREATE VIEW phonelistWithTeams AS
SELECT parent.parentID AS ParentID,
  ParentPerson.firstName AS ParentFirstName,
  ParentPerson.lastName AS  ParentLastName,
  ChildPerson.firstName AS PlayerFirstName,
  ChildPerson.lastName AS  PlayerLastName,
  team.teamName AS TeamName,
  GuardianHomePhone.homeNumber AS GuardianHomePhone
FROM parent
  JOIN player ON (parent.parentID IN (player.motherID, player.fatherID))
  JOIN person AS ParentPerson ON (ParentPerson.personID = parent.parentID)
  JOIN person AS  ChildPerson ON (ChildPerson.personID = player.playerID)
  JOIN addressDetails AS GuardianHomePhone ON (GuardianHomePhone.personID = parent.parentID)
  JOIN teamAllocation ON (ChildPerson.personID = teamAllocation.playerID)
  JOIN team ON (teamAllocation.teamID = team.teamID);

Running SELECT * FROM phonelistWithTeams will give you

+----------+-----------------+----------------+-----------------+----------------+------------+-------------------+
| ParentID | ParentFirstName | ParentLastName | PlayerFirstName | PlayerLastName | TeamName   | GuardianHomePhone |
+----------+-----------------+----------------+-----------------+----------------+------------+-------------------+
|        8 | Gregory         | Peck           | Michael         | Peck           | U10 Red    | 034871234         |
|        9 | Laura           | Peck           | Michael         | Peck           | U10 Red    | 034871234         |
|       10 | Martha          | Petersen       | Matt            | Petersen       | U11 Orange | 034724321         |
|       10 | Martha          | Petersen       | Christopher     | Petersen       | U10 Red    | 034724321         |
|       11 | Chris           | Michaels       | Richard         | Michaels       | U11 Orange | 034791212         |
|       11 | Chris           | Michaels       | Shaun           | Michaels       | U9 Yellow  | 034791212         |
|       12 | Nadine          | Michaels       | Richard         | Michaels       | U11 Orange | 034791212         |
|       12 | Nadine          | Michaels       | Shaun           | Michaels       | U9 Yellow  | 034791212         |
|       13 | Barry           | Dackers        | Harry           | Dackers        | U9 Yellow  | 034871996         |
|       14 | Kevin           | Mitchell       | Daniel          | Mitchell       | U9 Yellow  | 034742886         |
|       15 | Rebecca         | Mitchell       | Daniel          | Mitchell       | U9 Yellow  | 034742886         |
+----------+-----------------+----------------+-----------------+----------------+------------+-------------------+