Join Bill Weinman for an in-depth discussion in this video Using multiple related tables, part of SQL Essential Training (2014).
In practical terms, it's very common to have tables with many to many relationships. For example, a single customer may buy many items and a single item may be purchased by many customers. This is often implemented with adjuction table. Here's a simple and practical example. Be using the test.db database for this example. And lets start by just looking at the table structures in the test database. So here's the three tables in this database. There's customers, there's items, and there's sales.
And you notice each of these tables has an ID column. The ID column identifies a specific row in each table. The sale table performs the role of a junction table. Its item ID and customer ID columns allow the other two tables to have a many to many relationship. That is a customer may be related to many items and an item may be related to many customers. A junction table is a common way to accomplish this. Junction tables are common in the database world, sometimes linking many tables, and sometimes performing many separate roles.
Let's take a look at an example of a query that uses this junction table. So we're selecting, from the customer table the name column as customer. And from the item table, the name column. As item. And from the sale table, the price column, as price. Or from clause, use the customer table So that would be analogous with the left table in the join. We're actually going to have two joins. We're first going to join the sale table. And we'll join it on the intersection of the customer ID in the sale table.
And the ID column in the customer table. And so the condition is that these two columns must be equal And our other joined clause joins the item table as I. And this joins it on the condition of the item ID column from the sale table, being equal to the ID column in the item table. And we're going to order this by customer and item within customer So there's our joint query, and you see the sale table is being used as the junction table here.
So I'll press Go. And there's our result. So, customer, Bill Smith, bought this item, bot of 64 pixels for $29.95. Bob Smith bought a bar code for $9.99. And, Mary Smith, actually bought three items. She bought two box of 64 pixels for $29.95 each, and one sense of humor for $19.95. So that's a result from three different tables effectively. Because the price column is actually in the junction table. And it allows this many to many relationship.
Mary Smith is related to two different items. One of them twice. And this box of 64 pixels is related to two different customers. Now you might want to have a report that also includes customers without sales. And you can use a left outer join for this. First we're going to add a customer with no sales. And I'm going to put that up here above our SELECT statement. And then I'm going to change our JOINs to LEFT JOINs. So both of these have to be LEFT JOINs, because both them are using the Junction table.
And when I press Go, you see we now have this additional row, with Jane Smith, that has not bought any items at any price. So our left outer join includes all the rows where the condition is met plus any rows in the left table, and remember our left table is the one in the from clause, customer, even though that row does not have any corresponding rows in the right hand tables. So now I'm going to restore the database to it's original state by deleting that additional customer, first find out it's ID.
And it's ID is number 4. So I'm going to delete from customer where ID equals 4. And one row affected, so out database is now restored to its original state. Many to many relationships are common in relational database applications. And the junction table is a very common tool for managing these relationships.
- Understanding SQL terminology and syntax
- Creating new tables and records
- Inserting and updating data
- Writing basic SQL queries
- Sorting and filtering
- Accessing related tables with JOIN
- Working with strings
- Finding the numeric type of a value
- Using aggregate functions and transactions
- Updating a table with triggers
- Creating views
Skill Level Beginner
Q: For Mac OS X: When I try to start the Apache Web Server from the XAMPP control panel, it doesn't start, and when I open "localhost" in my web browser, I see a white screen that says "It Works!" instead of the XAMPP page.
sudo apachectl stop
Q: I'm on a Mac, and I get an error in SID that says "attempt to write a read only database." How can I fix this?
A: This usually means that the database folder does not have sufficient permissions for writing by the web user. This can happen if you create the SQL folder new, rather than copying it from the Exercise Files. Here's how to fix this:
- Open a Finder window and Navigate to /Applications/XAMPP/htdocs/SQL
- Control-click on the SQL folder and select "Get Info" from the context menu.
- Under "Sharing and Permissions" (you may need to open the disclosure triangle), in the "everyone" row, select "Read & Write."Then you can close the Info window.
- Now repeat the process for the three *.db files inside the folder.