CODEWITHSUNDEEP
mysqlsqldatabasedatabase-view
penguin.25
penguin.25

Reputation: 23

Need assistance with creating view in my database sql

Click img for schema

I'm trying to build a view based on the schema I have. But right now I am completely lost where to start. My view should consists of information about the player their(LINKING) guardian, guardian's phone number and what team they currently playing. I would really appreciate if someone could help me get to the right path. Please see link for attached image

Thanks!

Im hoping for this kind of result

 +----------+--------------+-------------+----------+-----------+-----------+------------+
|guardianID|guardian.fName|guardianlName|Contact   |playerfName|playerlName|teamName    |
+----------+--------------+-------------+----------+------------+-----------+-----------+
|        1 |Dora          | Kruger      |0288235556|Daphne     |Kruger     |Taeiri Eels |
|        2 |Tonia         | Wesley      |0204156513|Brigitta   |Wesley     |Southern Mag|
|        3 |Shanene       | Genovese    |0211915599|Stefan     |Genovese   |Kaikorai    |
|        4 |Jacob         | Jefferson   |0282316812|Yvonne     |Jefferson  |Pirate Skull|
|        5 |Tamika        | Longstaff   |0264217492|Todd       |Longstaff  |Zingari     |
+----------+--------------+------------+-----------+-----------+-----------+------------+

I have these following data

====PERSON TABLE====
+----------+--------------+-------------+----------+-----------+
|personID  |firstName     |lastName     |addressID |photo      |
+----------+--------------+-------------+----------+-----------+
|        1 |Dora          | Kruger      |1         |NULL       |
|        2 |Daphne        | Kruger      |2         |NULL       |
|        3 |Tonia         | Wesley      |3         |NULL       |
|        4 |Brigitta      | Wesley      |4         |NULL       |
|        5 |Shanene       | Genovese    |5         |NULL       |
|        6 |Stefan        | Genovese    |6         |NULL       |
|        7 |Jacob         | Jefferson   |7         |NULL       |
|        8 |Yvibbe        | Jefferson   |8         |NULL       |
|        9 |Tamika        | Longstaff   |9         |NULL       |
|        10|Todd          | Longstaff   |10        |NULL       |
+----------+--------------+------------+-----------+-----------+    


====PHONE TABLE====
+----------+--------------+
|phoneID   |primaryContact|
+----------+--------------+
|        1 |0288235556    |
|        2 |0204156513    |
|        3 |0211915599    |
|        4 |0282316812    |
|        5 |0264217492    |
+----------+--------------+

====GUARDIAN TABLE====
+----------+--------------+-------------+----------+
|guardianID|personID      |phoneID      |email     |
+----------+--------------+-------------+----------+
|        1 |  1           | 1           |@ya.com   |
|        2 |  3           | 2           |@yy.com   |
|        3 |  5           | 3           |@ll.com   |
|        4 |  7           | 4           |@pp.com   |
|        5 |  9           | 5           |@no.com   |
+----------+--------------+------------+-----------+    

====PLAYER TABLE====
+----------+--------------+-------------+--------------+
|playerId  |personID      |schoolID     |dob           |
+----------+--------------+-------------+--------------+
|        1 |  2           | 1           |2008-04-06    |
|        2 |  4           | 2           |2011-11-19    |
|        3 |  6           | 3           |2011-01-07    |
|        4 |  8           | 4           |2006-01-01    |
|        5 |  10          | 5           |2004-04-06    |
+----------+--------------+-------------+--------------+


    ====FAMILY TABLE====
+----------+--------------+
|playerID   |guardianID    |
+----------+--------------+
|        1 |1             |
|        2 |2             |
|        3 |3             |
|        4 |4             |
|        5 |5             |
+----------+--------------+


====TEAM TABLE====
+----------+------------------+-------------+--------------+--------------+
|teamID    |teamName          |year         |minAge        |maxAge        |
+----------+------------------+-------------+--------------+--------------+
|        1 |  Taieri Eels     | 2015-01-01  |5             |6             |
|        2 |  Southern Magpies| 2014-01-01  |7             |8             |
|        3 |  Kaikorai        | 2016-01-01  |7             |10            |
|        4 |  Pirate Skulls   | 2014-01-01  |11            |12            |
|        5 |  Zingari         | 2013-01-01  |12            |16            |
+----------+------------------+-------------+--------------+--------------+


    ====TEAM ALLOCATION TABLE====  // allocating team for each player
+----------+--------------+
|teamID    |playerID      |
+----------+--------------+
|        2 |1             |
|        5 |2             |
|        1 |3             |
|        3 |4             |
|        4 |5             |
+----------+--------------+

UPDATE: the guardian table is meant to identify the guardian of the player. We were trying to use the family table to link player with their guardian, as the relationship is many-to-many. However we are struggling trying to write the query. I was thinking it was a three table join and to use aliases? But I can't get it to work.

SELECT p1.firstName AS guardianFName, p1.lastName AS guardianLName,
p2.firstName AS playerFName, p2.lastName AS playerLName
from person AS p1, person as p2
join guardian on
person.personID = guardian.personID join
family on guardian.guardianID = family.guardianID
join player on 
person.personID = player.personID join
family on player.playerID = family.playerID;

I've tried a couple of variations like this

DDL and DML scripts is in http://pastebin.com/euVanc2e for those who want to help test the queries with data we have.

Upvotes: 0

Views: 113

Answers (1)

Darwin von Corax
Darwin von Corax

Reputation: 5256

Normalizing your schema would simplify things considerably, but that is best handled as a separate question.

The best way to create a complex join like the one you need is to build it up out of simple joins, testing at each step to make certain you're on the right track. The only real stumbling block for an inexperienced dev in this case is the need to join person twice; this can be addressed by using table aliases.

Let's break it down.

Of course, the first step is to join guardian to person to get the guardians' names:

SELECT g.guardianId AS guardianId,
       p1.firstName AS guardianFName, p1.lastName AS guardianLName
  FROM guardian g
  INNER JOIN person p1
    ON g.personId = p1.personId;

My reason for aliasing person as p1 and not simply as p will become clear in a moment.

Next, we do the same with player and person to get the players' names:

SELECT pr.playerId AS playerId,
       p2.firstName AS playerFName, p2.lastName AS playerLName
  FROM player pr
  INNER JOIN person p2
    ON pr.personId = p2.personId;

Again, I've aliased person as p2 and not simply p. The column playerId won't be displayed by the final query; it's here merely for illustrative purposes.

Now we want to match the guardians' names to the players' names. This means that for each row in the result we need to read two rows from person. We do this by creating two references to person using two different aliases. We simply join the results of the two previous steps through the family table, like so:

SELECT g.guardianId AS guardianId,
       p1.firstName AS guardianFName, p1.lastName AS guardianLName,
       p2.firstName AS playerFName, p2.lastName AS playerLName
  FROM guardian g
  INNER JOIN person p1
    ON g.personId = p1.personId
  INNER JOIN family f
    ON g.guardianId = f.guardianId
  INNER JOIN player pr
    ON f.playerId = pr.playerId
  INNER JOIN person p2
    ON pr.personId = p2.personId;

Note the second reference to person, aliased as p2. This gives the second reference to the table, allowing the result to contain data from different rows in the two references.

Getting the phone numbers is a simple one-step join to phone.

Getting the team names requires a two-step join to team_allocation, then to team. You simply don't return any columns from team_allocation.

The complete query should look something like this:

SELECT g.guardianId AS guardianId,
       p1.firstName AS guardianFName, p1.lastName AS guardianLName,
       ph.primaryContact AS contact,
       p2.firstName AS playerFName, p2.lastName AS playerLName,
       t.teamName AS teamName
  FROM guardian g
  INNER JOIN person p1
    ON g.personId = p1.personId
  INNER JOIN family f
    ON g.guardianId = f.guardianId
  INNER JOIN player pr
    ON f.playerId = pr.playerId
  INNER JOIN person p2
    ON pr.personId = p2.personId
  INNER JOIN phone ph
    ON g.phoneId = ph.phoneId
  INNER JOIN team_allocation ta
    ON ta.playerId = pr.playerId
  INNER JOIN team t
    ON ta.teamId = t.teamId;

Hope that helps.

Upvotes: 1

Related Questions