![]() ![]() Now we will create an INNER JOIN between these two tables. Run the queries below in your PostgreSQL console and follow along to view results in the users and addresses tables. Let's take a look at the data in the users table. In the data we set up, we have 3 users, but only 2 users have addresses. In the previous chapter, we designed the user and address table to have a one-to-one relationship. To demonstrate how INNER JOINs work, we will join the users and addresses tables. We have intentionally setup the data in the following ways: VALUES(3, 'My Third SQL book','Cary Flint', NOW()) INSERT INTO users (id, username) VALUES (3, 'Alice Munro') We do also add one extra book, but it isn't checked out by any users. ![]() The third user, Alice Munro, has no address, no books and no reviews. INSERT INTO reviews (id, book_id, user_id, review_content ) VALUES (2, '2 Elm Street', 'San Francisco', 'CA') INSERT INTO users (id, username) VALUES (2, 'Jane Smiley') She has an address at 2 Elm Street, San Francisco, CA, and has 1 book My Second SQL Book with a review by Jane Smiley. The second set of statements set up the data for the second user, Jane Smiley. Book: My Second SQL Book by John Mayer with a review Jane Smiley's review.Address: 2 Elm Street, San Francisco, CA.VALUES(2, 'My Second SQL book','John Mayer', NOW()) INSERT INTO reviews (id, book_id, user_id, review_content) INSERT INTO users_books (user_id, book_id, checkout_date) VALUES(1, 'My First SQL book', 'Mary Parker', NOW()) INSERT INTO books (id, title, author, published_date) ![]() VALUES (1, '1 Market Street', 'San Francisco', 'CA') INSERT INTO addresses (user_id, street, city, state) INSERT INTO users (id, username) VALUES (1, 'John Smith') John Smith also has 2 reviews for each of the books. John Smith has an address at 1 Market Street, San Francisco, CA, and has checked out 2 books: My First SQL Book and My Second SQL Book. ![]() The first set of statements set up the data for the first user, John Smith.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |