An Intro to SQL Part 2 Joins

By Charles McDowell

Comptia Certified Linux Networking Professional.

Cisco Certified Networking Technician.

Microsoft Technology Associate Database Administration

View fullsize

“It is only when they go wrong that machines remind you how powerful they are.”

— Clive James

Joins

We would now like to see the names of all Products (not unique) written by “Dan Brown” that were borrowed, along with the date of return :

SELECT products.productname AS "Name", 
        Accounts.releasedate AS "Release Date"
FROM Accounts 
JOIN Products ON acounts.productid=products.productid
WHERE products < 30 ; 

The result is :

Name

Release Date

Calorie Calculator

2020-06-13 00:00:00

Fitness Time Tracker

2020-02-23 00:00:00

You may notice the differences in the ‘From’ section of the query. We are now querying a table formed by joining these accounts and products tables.

Now let’s try to join 2 tables.

Let’s also find the first name and last name of everyone who has borrowed a book written by “Jane Doe”.

• Step 1 - We want to join the tables on the columns that are the same between tables:

JOIN books ON borrowings.bookid=books.bookid

JOIN members ON members.memberid=borrowings.memberid

• Step 2 - What data should we show? We are only concerned with data where the author is “Jane Doe”

WHERE books.author='Jane Doe'

• Step 3 - How should we show it? Now that we got the data we want, we just want to show the first name and the last name of the members who borrowed it :

SELECT members.firstname AS "First Name",
       members.lastname AS "Last Name"
FROM borrowings
JOIN books ON borrowings.bookid=books.bookid
JOIN members ON members.memberid=borrowings.memberid
WHERE books.author='Dan Brown';

·      

Which gives us :

First Name

Last Name

Jesse

Roberts

Larry

Johnson

Larry

Johnson

Great work! You’ll notice the names are not unique but I’ll show how to resolve that next time.

Stay tuned for part 3 next week.