Нужна помощь с созданием представления в моей базе данных SQL
Я пытаюсь построить представление на основе имеющейся схемы. Но сейчас я совершенно потерян, с чего начать. Мое мнение должно состоять из информации об игроке, его (ССЫЛКА) опекуна, номер телефона опекуна и какой команды они играют в настоящее время. Я был бы очень признателен, если бы кто-нибудь мог помочь мне выбрать правильный путь. Пожалуйста, смотрите ссылку для прикрепленного изображения
Спасибо!
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 |
+----------+--------------+------------+-----------+-----------+-----------+------------+
У меня есть следующие данные
====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 |
+----------+--------------+
ОБНОВЛЕНИЕ: таблица опекуна предназначена для идентификации опекуна игрока. Мы пытались использовать семейный стол, чтобы связать игрока со своим опекуном, так как отношения многие-ко-многим. Однако мы пытаемся написать запрос. Я думал, что это три таблицы соединения и использовать псевдонимы? Но я не могу заставить его работать.
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;
Я пробовал пару вариантов, как это
Сценарии DDL и DML находятся по http://pastebin.com/euVanc2e для тех, кто хочет помочь протестировать запросы с имеющимися у нас данными.
1 ответ
Нормализация вашей схемы значительно упростила бы ситуацию, но это лучше всего рассматривать как отдельный вопрос.
Лучший способ создать сложное объединение, подобное тому, которое вам нужно, - это создать его из простых объединений, проверяя на каждом этапе, чтобы убедиться, что вы на правильном пути. Единственным камнем преткновения для неопытного разработчика в этом случае является необходимость присоединиться person дважды; это можно решить с помощью псевдонимов таблиц.
Давайте разберемся с этим.
Конечно, первый шаг - присоединиться guardian в person чтобы получить имена опекунов:
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;
Моя причина для алиасинга person как p1 а не просто как p станет ясно через мгновение.
Далее мы делаем то же самое с player а также person чтобы получить имена игроков:
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;
Опять я псевдоним person как p2 а не просто p, Колонка playerId не будет отображаться при окончательном запросе; это здесь только для иллюстративных целей.
Теперь мы хотим сопоставить имена опекунов с именами игроков. Это означает, что для каждой строки в результате нам нужно прочитать две строки из person, Мы делаем это путем создания двух ссылок на person используя два разных псевдонима. Мы просто объединяем результаты двух предыдущих шагов через family таблица, вот так:
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;
Обратите внимание на вторую ссылку на personс псевдонимом как p2, Это дает вторую ссылку на таблицу, позволяя результату содержать данные из разных строк в двух ссылках.
Получение телефонных номеров - простое присоединение к phone,
Для получения названий команд требуется двухэтапное соединение team_allocationзатем team, Вы просто не возвращаете столбцы из team_allocation,
Полный запрос должен выглядеть примерно так:
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;
Надеюсь, это поможет.