CODEWITHSUNDEEP
mysqljoin
Ben
Ben

Reputation: 371

Combining Four Tables - MySQL

I need to extract data from four tables in an after-school activity scheme database: Schedule, Activities, Children, and Parents.

Schedule contains a primary key, a reference to the activity, and a reference to the child.

Firstly I need to obtain an ID from the Activities table for my chosen activity (e.g. search for "football" and get the corresponding ID). I then need to use this to search in the Schedule table for each child that is doing that activity and display these details. Each entry in the Children table holds a reference to a Parent. I finally need to extract this reference so I can display the name of the child's parent.

The final result should look like this:

activity_id | child_name | parent_name

All advice greatly appreciated! :)

FYI, here are my table schemas:

<!--CREATE CHILD TABLE-->

CREATE TABLE Child (
    id INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
    forename VARCHAR(30) NOT NULL,
    surname VARCHAR(30) NOT NULL,
    gender ENUM('male', 'female') NOT NULL,
    dob DATE NOT NULL,
    guardian INT NOT NULL,
)


<!--CREATE GUARDIAN TABLE-->

CREATE TABLE Guardian (
    id INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
    title VARCHAR(5) NOT NULL,
    forename VARCHAR(30) NOT NULL,
    surname VARCHAR(30) NOT NULL,
    address1 VARCHAR(30) NOT NULL,
    address2 VARCHAR(30),
    town VARCHAR(30) NOT NULL,
    county VARCHAR(30),
    postcode VARCHAR(10) NOT NULL,
    telephone VARCHAR(20) NOT NULL
)


<!--CREATE ACTIVITY TABLE-->

CREATE TABLE Activity (
    id INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
    name VARCHAR(30) NOT NULL,
    day ENUM('Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday', 'Sunday') NOT NULL
)


<!--CREATE SCHEDULE TABLE-->

CREATE TABLE Schedule (
    id INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
    child_id INT NOT NULL,
    activity_id INT NOT NULL,
    date DATE NOT NULL
)

Upvotes: 1

Views: 103

Answers (1)

Neil
Neil

Reputation: 55402

SELECT Activity.id, Child.forename, Child.surname, Guardian.forename, Guardian.surname
  FROM Activity
  INNER JOIN Schedule ON Schedule.activity_id = Activity.id
  INNER JOIN Child ON Schedule.child_id = Child.id
  INNER JOIN Guardian ON Child.guardian_id = Guardian.id
  WHERE Activity.name = 'football'

Upvotes: 1

Related Questions