Topic Review

Topic Review - Joins

Objectives

Use joins to get custom select queries.

Setup

Start with the create and insert statements already done.

/* create.sql */

CREATE TABLE projects(
id INTEGER PRIMARY KEY AUTOINCREMENT,
title TEXT,
category TEXT,
funding_goal INTEGER,
start_date TEXT,
end_date TEXT
);

create table users(
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT,
age INTEGER
);

create table pledges(
id INTEGER PRIMARY KEY AUTOINCREMENT,
amount INTEGER,
user_id INTEGER REFERENCES user,
project_id INTEGER REFERENCES project
);

/* insert.sql */
insert into users(name, age) VALUES
('frank', 12),
('jim', 24),
('mike', 23),
('shelley', 12),
('bob', 43),
('rob', 13),
('tim', 43),
('vinny', 27),
('nancy', 20),
('north', 28),
('par', 22),
('kim', 30),
('trina', 39),
('mc hammer', 54),
('shia', 45),
('mark', 44),
('mark', 23),
('jim', 11),
('kim', 23),
('jess', 24);


insert into projects(title, category, funding_goal, start_date, end_date)
 VALUES
 ('Solve the meaning of life', 'logic', 450000, 01/31/2015, 05/25/2016),
 ('cure stupidity', 'logic', 2400, 04/12/2012, 04/20/2015),
 ('dance party', 'physical', 100, 11/12/2011, 12/1/2012),
 ('stop world hunger', 'life', 55545000, 01/31/2015, 12/31/2016),
 ('win lottery', 'life', 5, 01/31/2015, 02/02/2015),
 ('anoter project name', 'impossible', 4545000, 04/02/2022, 06/20/2026),
 ('fly to mars', 'space', 111039, 01/31/2010, 09/20/2019),
 ('eat all the skittles', 'food', 50, 01/31/2015, 01/25/2016),
 ('burn this mother down', 'destructive', 2, 05/19/2014, 08/2/2019),
 ('world destruction', 'destructive', 45000, 01/1/2111, 01/1/2112);


INSERT INTO pledges(amount, user_id, project_id) VALUES
   (1000, 1, 4),
   (12, 2, 1),
   (130, 3, 10),
   (100, 4, 6),
   (100, 5, 7),
   (100, 9, 9),
   (100, 11, 2),
   (100, 12, 4),
   (100, 17, 1),
   (100, 19, 6),
   (100, 1, 2),
   (100, 12, 9),
   (100, 18, 9),
   (100, 19, 8),
   (100, 12, 8),
   (100, 4, 4),
   (100, 5, 10),
   (100, 8, 2),
   (100, 9, 7),
   (100, 2, 9),
   (100, 7, 6),
   (100, 9, 2),
   (100, 10, 8),
   (100, 4, 4),
   (100, 10, 10),
   (100, 11, 8),
   (100, 14, 5),
   (100, 3, 9),
   (100, 13, 9),
   (100, 12, 1);

Intro

Now that we have data created, we can write SELECT statements to access it.
However, all of the data that we want isn't contained in a single table. We have three different tables which relate to each other using primary/foreign keys.
For this to work , we'll need to JOIN our tables together in our SELECT statements on the common value.

Selecting Titles and Pledge Amounts

Let's look at the first example: selects_the_titles_of_all_projects_and_their_pledge_amounts We can join the projects table with the pledges table because each pledge has a column for project_id.

def selects_the_titles_of_all_projects_and_their_pledge_amounts
  "SELECT projects.title, SUM(pledges.amount) FROM projects JOIN pledges ON projects.id = pledges.project_id GROUP BY projects.title"
end

Let's break this down.

SELECT kicks off our select statement, followed by the data we want to return (in this case, title of projects and the sum of their pledge amounts).
FROM identifies the table or tables we want to collect data from. We can only specify one table, but using a JOIN statement, we can combine two or more tables into one.
In this case, we're collecting data from projects JOIN pledges ON projects.id = pledges.project_id - this essentially allows us to treat two separate tables as one.
Our GROUP BY clause tells us how to sum up our values. Without specifying a GROUP BY, we'll simply be given the sum of ALL of the pledges, regardless of what project they're associated with.
We could group by project.id or project.title since both are unique - in this case, we'll group by title because the spec wants the results in alphabetical order.

Selecting Titles Where the Funding Goal is Met

Here, we want the titles and surplus-pledge amount of any projects that have reached their funding goal.

def selects_the_titles_of_all_projects_that_have_met_their_funding_goal
  " SELECT projects.title, projects.funding_goal - sum(pledges.amount) AS total 
    FROM projects 
    JOIN pledges
    ON projects.id = pledges.project_id
    GROUP BY projects.title
    HAVING total <=  0;"
end

The basic structure is the same - we have a SELECT, JOIN, and GROUP statement.
We also want to select the different between the funding goal and the sum of pledges.
We can alias this amount (here we call it "total") to make it easier to reference again
Lastly, we add a HAVING clause to limit our results to only those with a total greater than or equal to 0.

BONUS - Defining our Own Method

Let's go outside of the spec and define a method that returns all of the users over 30 who donated to a specific category and how much they donated.

def users_over_30_who_fund_music
  'SELECT users.name, SUM(pledges.amount)
  FROM users 
  JOIN pledges ON pledges.user_id = users.id
  JOIN projects ON pledges.project_id = projects.id
  WHERE users.age >= 30 AND projects.category = "music"
  GROUP BY users.name'
end

This is the same deal as the others - here, we need to join both the pledges table and the projects table since we need data from all three tables.

Navigation Menu