Fezch
Reputation: 23
Create a View which spans multiple tables
I am trying to create a view in MySQL which shows a Player's name, their Guardians name, their Guardians phone number and the team the Player plays in.
I have this script which creates the database :
DROP TABLE IF EXISTS TeamCoach;
DROP TABLE IF EXISTS TeamPlayer;
DROP TABLE IF EXISTS CoachQualification;
DROP TABLE IF EXISTS PlayerGuardian;
DROP TABLE IF EXISTS PersonAddress;
DROP TABLE IF EXISTS PersonPhoneNumber;
DROP TABLE IF EXISTS Coach;
DROP TABLE IF EXISTS Player;
DROP TABLE IF EXISTS Qualification;
DROP TABLE IF EXISTS Team;
DROP TABLE IF EXISTS PhoneNumber;
DROP TABLE IF EXISTS School;
DROP TABLE IF EXISTS Person;
DROP TABLE IF EXISTS Address;
DROP TABLE IF EXISTS Guardian;
DROP FUNCTION IF EXISTS TeamSize;
CREATE TABLE Qualification (
qualificationID int NOT NULL AUTO_INCREMENT,
name varchar(30) NOT NULL,
level int NOT NULL,
PRIMARY KEY (qualificationID)
) ENGINE=InnoDB;
CREATE TABLE Team (
teamID int NOT NULL AUTO_INCREMENT,
name varchar(30) NOT NULL,
ageGroup varchar(30) NOT NULL,
year int NOT NULL,
PRIMARY KEY (teamID)
) ENGINE=InnoDB;
CREATE TABLE Address (
addressID int NOT NULL AUTO_INCREMENT,
number int NOT NULL,
street varchar(30) NOT NULL,
suburb varchar(30) NOT NULL,
townCity varchar(30) NOT NULL,
PRIMARY KEY (addressID)
) ENGINE=InnoDB;
CREATE TABLE PhoneNumber (
phoneNumberID int NOT NULL AUTO_INCREMENT,
number varchar(20) NOT NULL,
PRIMARY KEY (phoneNumberID)
) ENGINE=InnoDB;
CREATE TABLE School (
schoolID int NOT NULL AUTO_INCREMENT,
name varchar(30) NOT NULL,
PRIMARY KEY (schoolID)
) ENGINE=InnoDB;
CREATE TABLE Person (
personID int NOT NULL AUTO_INCREMENT,
name varchar(30) NOT NULL,
email varchar(30) NOT NULL,
photo varchar(30) NOT NULL,
PRIMARY KEY (personID)
) ENGINE=InnoDB;
CREATE TABLE PersonAddress (
personID int NOT NULL,
addressID int NOT NULL,
KEY personID (personID),
KEY addressID (addressID),
FOREIGN KEY (personID) REFERENCES Person (personID),
FOREIGN KEY (addressID) REFERENCES Address (addressID)
) ENGINE=InnoDB;
CREATE TABLE PersonPhoneNumber (
personID int NOT NULL,
phoneNumberID int NOT NULL,
KEY personID (personID),
KEY phoneNumberID (phoneNumberID),
FOREIGN KEY (personID) REFERENCES Person (personID),
FOREIGN KEY (phoneNumberID) REFERENCES PhoneNumber (phoneNumberID)
) ENGINE=InnoDB;
CREATE TABLE Coach (
coachID int NOT NULL PRIMARY KEY REFERENCES Person (personID),
dateBeganCoaching varchar(10) NOT NULL
) ENGINE=InnoDB;
CREATE TABLE Player (
playerID int NOT NULL PRIMARY KEY REFERENCES Person (personID),
DOB varchar(10) NOT NULL,
schoolID int NOT NULL,
KEY schoolID (schoolID),
FOREIGN KEY (schoolID) REFERENCES School (schoolID)
) ENGINE=InnoDB;
CREATE TABLE Guardian (
guardianID int NOT NULL PRIMARY KEY REFERENCES Person (personID)
)ENGINE=InnoDB;
CREATE TABLE PlayerGuardian (
guardianID int NOT NULL,
playerID int NOT NULL,
KEY guardianID (guardianID),
KEY playerID (playerID),
FOREIGN KEY (guardianID) REFERENCES Guardian (guardianID),
FOREIGN KEY (playerID) REFERENCES Player (playerID)
) ENGINE=InnoDB;
CREATE TABLE TeamPlayer (
teamID int NOT NULL,
playerID int NOT NULL,
KEY teamID (teamID),
KEY playerID (playerID),
FOREIGN KEY (teamID) REFERENCES Team (teamID),
FOREIGN KEY (playerID) REFERENCES Player (playerID)
) ENGINE=InnoDB;
CREATE TABLE TeamCoach (
teamID int NOT NULL,
coachID int NOT NULL,
KEY teamID (teamID),
KEY coachID (coachID),
FOREIGN KEY (teamID) REFERENCES Team (teamID),
FOREIGN KEY (coachID) REFERENCES Coach (coachID)
) ENGINE=InnoDB;
CREATE TABLE CoachQualification (
coachID int NOT NULL,
qualificationID int NOT NULL,
KEY coachID (coachID),
KEY qualificationID (qualificationID),
FOREIGN KEY (coachID) REFERENCES Coach (coachID),
FOREIGN KEY (qualificationID) REFERENCES Qualification (qualificationID)
) ENGINE=InnoDB;
DELIMITER //
CREATE FUNCTION TeamSize(Team varchar(30))
RETURNS int
DETERMINISTIC CONTAINS SQL
BEGIN
DECLARE Size int;
SELECT COUNT(*) INTO Size FROM ((
SELECT * FROM TeamPlayer WHERE teamID=(
SELECT teamID FROM Team WHERE name='Red Bulls')))AS TeamSize;
RETURN Size;
END //
DELIMITER ;
And this script which fills it with data :
INSERT INTO Qualification (name, level) VALUES ('Under 7s', '3');
INSERT INTO Qualification (name, level) VALUES ('Under 8s', '1');
INSERT INTO Qualification (name, level) VALUES ('Under 9s', '5');
INSERT INTO Qualification (name, level) VALUES ('Under 10s', '4');
INSERT INTO Qualification (name, level) VALUES ('Under 80s', '10');
INSERT INTO Team (name, ageGroup, year) VALUES ('Blue Hawks', 'Under 7s', '2015');
INSERT INTO Team (name, ageGroup, year) VALUES ('Yellow Dolphins', 'Under 8s', '2013');
INSERT INTO Team (name, ageGroup, year) VALUES ('Red Bulls', 'Under 9s', '2014');
INSERT INTO Team (name, ageGroup, year) VALUES ('Turquiose Turtles', 'Under 10s', '2015');
INSERT INTO Team (name, ageGroup, year) VALUES ('Violet Butterflies', 'Under 80s', '2015');
INSERT INTO Address (number, street, suburb, townCity) VALUES ('6', 'Selwyn Street', 'North East Valley', 'Dunedin');
INSERT INTO Address (number, street, suburb, townCity) VALUES ('6', 'Inverleith Street', 'Woodhaugh', 'Dunedin');
INSERT INTO Address (number, street, suburb, townCity) VALUES ('40', 'Chaucer Street', 'Milton', 'Milton');
INSERT INTO Address (number, street, suburb, townCity) VALUES ('105', 'Inniscort Street', 'Decent Part', 'Cromwell');
INSERT INTO Address (number, street, suburb, townCity) VALUES ('43', 'Chambers Street', 'North East Valley', 'Dunedin');
INSERT INTO PhoneNumber (number) VALUES ('034178669');
INSERT INTO PhoneNumber (number) VALUES ('0272637393');
INSERT INTO PhoneNumber (number) VALUES ('0277147957');
INSERT INTO PhoneNumber (number) VALUES ('0220826217');
INSERT INTO PhoneNumber (number) VALUES ('0800838383');
INSERT INTO School (name) VALUES ('Tokomairiro High');
INSERT INTO School (name) VALUES ('Cromwell College');
INSERT INTO School (name) VALUES ('Otago Boys');
INSERT INTO School (name) VALUES ('Otago Girls');
INSERT INTO School (name) VALUES ('Woodhaugh Rest Palace');
INSERT INTO Person (name, email, photo) VALUES ('Andrew Fletcher', '[email protected]', 'puppy.jpg');
INSERT INTO Person (name, email, photo) VALUES ('Sam Bates', '[email protected]', 'kitten.jpg');
INSERT INTO Person (name, email, photo) VALUES ('Mason Osbourne', '[email protected]', 'cheetah.jpg');
INSERT INTO Person (name, email, photo) VALUES ('Zara DeMontgomery', '[email protected]', 'elephant.jpg');
INSERT INTO Person (name, email, photo) VALUES ('Reuben Crimp', '[email protected]', 'dolphins.jpg');
INSERT INTO Person (name, email, photo) VALUES ('Joy Gasson', '[email protected]', 'owl.jpg');
INSERT INTO Person (name, email, photo) VALUES ('Brian Treanor', '[email protected]', 'whale.jpg');
INSERT INTO Person (name, email, photo) VALUES ('Dale Parsons', '[email protected]', 'swordfish.jpg');
INSERT INTO Person (name, email, photo) VALUES ('Tom Clark', '[email protected]', 'lion.jpg');
INSERT INTO Person (name, email, photo) VALUES ('Jim Beam', '[email protected]', 'monkey.jpg');
INSERT INTO Person (name, email, photo) VALUES ('Jack Daniels', '[email protected]', 'monkey.jpg');
INSERT INTO Person (name, email, photo) VALUES ('John Snow', '[email protected]', 'monkey.jpg');
INSERT INTO Person (name, email, photo) VALUES ('Ned Stark', '[email protected]', 'monkey.jpg');
INSERT INTO Person (name, email, photo) VALUES ('Tywin Lannister', '[email protected]', 'monkey.jpg');
INSERT INTO Person (name, email, photo) VALUES ('Hodor Hodor', '[email protected]', 'monkey.jpg');
INSERT INTO Person (name, email, photo) VALUES ('Joe Bloggs', '[email protected]', 'monkey.jpg');
INSERT INTO Person (name, email, photo) VALUES ('John Doe', '[email protected]', 'monkey.jpg');
INSERT INTO Person (name, email, photo) VALUES ('Jane Doe', '[email protected]', 'monkey.jpg');
INSERT INTO Person (name, email, photo) VALUES ('Lassie Dog', '[email protected]', 'monkey.jpg');
INSERT INTO Person (name, email, photo) VALUES ('Jake Sully', '[email protected]', 'monkey.jpg');
INSERT INTO PersonAddress (personID, addressID) VALUES ('1', '3');
INSERT INTO PersonAddress (personID, addressID) VALUES ('2', '4');
INSERT INTO PersonAddress (personID, addressID) VALUES ('3', '5');
INSERT INTO PersonAddress (personID, addressID) VALUES ('4', '3');
INSERT INTO PersonAddress (personID, addressID) VALUES ('5', '2');
INSERT INTO PersonPhoneNumber (personID, phoneNumberID) VALUES ('1', '2');
INSERT INTO PersonPhoneNumber (personID, phoneNumberID) VALUES ('2', '4');
INSERT INTO PersonPhoneNumber (personID, phoneNumberID) VALUES ('3', '1');
INSERT INTO PersonPhoneNumber (personID, phoneNumberID) VALUES ('4', '5');
INSERT INTO PersonPhoneNumber (personID, phoneNumberID) VALUES ('5', '3');
INSERT INTO Coach (coachID, dateBeganCoaching) VALUES ('6', '2014');
INSERT INTO Coach (coachID, dateBeganCoaching) VALUES ('7', '2013');
INSERT INTO Coach (coachID, dateBeganCoaching) VALUES ('8', '2012');
INSERT INTO Coach (coachID, dateBeganCoaching) VALUES ('9', '2014');
INSERT INTO Coach (coachID, dateBeganCoaching) VALUES ('10', '1993');
INSERT INTO Player (playerID, DOB, schoolID) VALUES ('1', '08/07/1993', '1');
INSERT INTO Player (playerID, DOB, schoolID) VALUES ('2', '02/06/1993', '2');
INSERT INTO Player (playerID, DOB, schoolID) VALUES ('3', '08/04/1995', '1');
INSERT INTO Player (playerID, DOB, schoolID) VALUES ('4', '08/01/1994', '1');
INSERT INTO Player (playerID, DOB, schoolID) VALUES ('5', '25/12/1992', '5');
INSERT INTO Player (playerID, DOB, SchoolID) VALUES ('11', '06/07/1998', '3');
INSERT INTO Player (playerID, DOB, SchoolID) VALUES ('12', '06/07/1998', '3');
INSERT INTO Player (playerID, DOB, SchoolID) VALUES ('13', '06/07/1998', '4');
INSERT INTO Player (playerID, DOB, SchoolID) VALUES ('14', '06/07/1998', '5');
INSERT INTO Player (playerID, DOB, SchoolID) VALUES ('15', '06/07/1998', '4');
INSERT INTO Guardian (guardianID) VALUES ('16');
INSERT INTO Guardian (guardianID) VALUES ('17');
INSERT INTO Guardian (guardianID) VALUES ('18');
INSERT INTO Guardian (guardianID) VALUES ('19');
INSERT INTO Guardian (guardianID) VALUES ('20');
INSERT INTO PlayerGuardian (guardianID, playerID) VALUES ('16', '1');
INSERT INTO PlayerGuardian (guardianID, playerID) VALUES ('16', '2');
INSERT INTO PlayerGuardian (guardianID, playerID) VALUES ('17', '3');
INSERT INTO PlayerGuardian (guardianID, playerID) VALUES ('17', '4');
INSERT INTO PlayerGuardian (guardianID, playerID) VALUES ('18', '5');
INSERT INTO PlayerGuardian (guardianID, playerID) VALUES ('18', '11');
INSERT INTO PlayerGuardian (guardianID, playerID) VALUES ('19', '12');
INSERT INTO PlayerGuardian (guardianID, playerID) VALUES ('19', '13');
INSERT INTO PlayerGuardian (guardianID, playerID) VALUES ('20', '14');
INSERT INTO PlayerGuardian (guardianID, playerID) VALUES ('20', '15');
INSERT INTO TeamPlayer (teamID, playerID) VALUES ('1', '1');
INSERT INTO TeamPlayer (teamID, playerID) VALUES ('2', '2');
INSERT INTO TeamPlayer (teamID, playerID) VALUES ('3', '3');
INSERT INTO TeamPlayer (teamID, playerID) VALUES ('4', '4');
INSERT INTO TeamPlayer (teamID, playerID) VALUES ('5', '5');
INSERT INTO TeamPlayer (teamID, playerID) VALUES ('2', '11');
INSERT INTO TeamPlayer (teamID, playerID) VALUES ('4', '12');
INSERT INTO TeamPlayer (teamID, playerID) VALUES ('4', '13');
INSERT INTO TeamPlayer (teamID, playerID) VALUES ('3', '14');
INSERT INTO TeamPlayer (teamID, playerID) VALUES ('5', '15');
INSERT INTO TeamCoach (teamID, coachID) VALUES ('1', '6');
INSERT INTO TeamCoach (teamID, coachID) VALUES ('2', '7');
INSERT INTO TeamCoach (teamID, coachID) VALUES ('3', '8');
INSERT INTO TeamCoach (teamID, coachID) VALUES ('4', '9');
INSERT INTO TeamCoach (teamID, coachID) VALUES ('5', '10');
INSERT INTO CoachQualification (coachID, qualificationID) VALUES ('6', '5');
INSERT INTO CoachQualification (coachID, qualificationID) VALUES ('7', '4');
INSERT INTO CoachQualification (coachID, qualificationID) VALUES ('8', '3');
INSERT INTO CoachQualification (coachID, qualificationID) VALUES ('9', '2');
INSERT INTO CoachQualification (coachID, qualificationID) VALUES ('10', '1');
Is what I am trying to do even possible?
Upvotes: 2
Views: 54
Answers (1)
Blue
Reputation: 22911
I've attempted to create a query that will work for your data, but this is assuming you only have one guardian for each player (Which you said in your comment that this is not possible). Here is the SQLFiddle if you want to play around with it a bit more.
SELECT p.name, pg.name AS `GuardianName`, pgpn.`number` AS `GuardianNumber`
FROM `Player` play
LEFT JOIN `Person` p ON play.playerID = p.personID
LEFT JOIN `PlayerGuardian` g ON play.playerID = g.playerID
LEFT JOIN `Person` pg ON g.guardianID = pg.personID
LEFT JOIN `PersonPhoneNumber` pgppn ON pg.personID = pgppn.personID
LEFT JOIN `PhoneNumber` pgpn ON pgpn.phoneNumberID = pgppn.phoneNumberID
The problem you're having is, how do you display multiple guardians for each player? Do you want players to show up multiple times for each guardian that player has? Then you have to worry about grouping coding level, and this can be a big hassle.
My recommendation: Use an ORM similar to CakePHP that will do the heavy lifting for you. You set up the relationships between the tables, and it will do the magic of linking them up for you, in multiple efficient queries.
Upvotes: 1
Related Questions
- Combine multiple tables to generate a view
- create a view joining multiple tables and another separate tables to a single one
- SQL - Create view from multiple tables
- MYSQL - Creating a View With Multiple Tables
- Create multiple view tables from a single query
- Using CREATE VIEW with multiple tables
- How to create a view in MySQL with unrelated tables
- Creating a view with multiple tables?
- MySql - Create view to read from Multiple Tables
- Multiple tables in one view?