Multiple Tables with Joins

Part 1 - Initial Set Up with Queries

Setting up our 3 tables for books/authors and the join table

Initial ERD (Entity Relationship Diagram)/Schema for ybooksdb

I added a "y" to the table names to prevent naming conflicts as we are presently dumping everything into one actual Database (when we set up an AWS instance, we can properly put each schema into their own database)

ybooks ERD - 3 tables

Using WWW SQL Designer to design your DB

WWW SQL Designer
- Modified Copy of SQL Designer
- WWW SQL Designer Tutorial
IF you have to: set foreign_key_checks=0; but make SURE to set it back to 1 before you do anything else
I added in the engine/default charset and collate to the create tables and changed the collation to utf8mb4_unicode_ci (case insensitive)
REMOVE the "NULL" and "DEFAULT NULL" from the PK field (in MySQL 5.7+ these terms will break the table creation)
The Foreign Keys (FK) can usually be left NULL DEFAULT NULL
Also if you delete a primary key, like we did in the join table, the output of www sql designer, leaves in an empty primary key() field, fix this
Note, you DO NOT need to use the backquotes ( ` `) UNLESS you are using spaces in your table/field names or are using keywords in your field names, which is a BIG NO NO
www sql designer (and just about every database program) puts them in to be double safe, and it can be annoying

Somewhat cleaned output from www SQL Designer

    CREATE TABLE `ybooks_authors` (
    `book_id` INTEGER UNSIGNED,
    `author_id` INTEGER UNSIGNED,
    `author_order` TINYINT NULL DEFAULT NULL,
    UNIQUE KEY (book_id,author_id)
    )ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

    CREATE TABLE `ybooks` (
      `book_id` INTEGER UNSIGNED AUTO_INCREMENT,
      `title` VARCHAR(200) NULL DEFAULT NULL,
      `yearpub` YEAR NULL DEFAULT NULL,
      PRIMARY KEY (`book_id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

    CREATE TABLE `yauthors` (
      `author_id` INTEGER UNSIGNED AUTO_INCREMENT,
      `first_name` VARCHAR(30) NULL DEFAULT NULL,
      `last_name` VARCHAR(30) NULL DEFAULT NULL,
      `whereborn` VARCHAR(10) NULL DEFAULT NULL,
      PRIMARY KEY (`author_id`)
    )ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

    ALTER TABLE `ybooks_authors` ADD FOREIGN KEY (book_id) REFERENCES `ybooks` (`book_id`);
    ALTER TABLE `ybooks_authors` ADD FOREIGN KEY (author_id) REFERENCES `yauthors` (`author_id`);

Now to do the inserts

    INSERT INTO ybooks(title,yearpub) values
    ('Black Holes','1984'),
    ('1984', '1954'),
    ('MySQL for Relaxation','2022'),
    ('Sorcerors Stone', '1999'),
    ('Spellng for Dummys', '2017');

    INSERT INTO yauthors (first_name,last_name,whereborn) values
    ('Steven','Hawking','Oxford'),
    ('George','Orwell','England'),
    ('Albert','Einstein','NJ'),
    ('J.K.','Rowling','England'),
    ('Phil','Waclawski','MI'),
    ('Stan','Lee', 'NY');

    NOTE: This is really a bad way to do this, this assumes that you have the exact same 
    PK values as I did, but we do not have procedures nor an interface at this point, so 
    we do not have a lot of choice.

    INSERT INTO ybooks_authors (book_id,author_id,author_order) values
    (1,1,1),
    (1,3,2),
    (1,6,3),
    (2,2,1),
    (2,4,2),
    (3,5,1),
    (4,4,1),
    (4,6,2),
    (5,5,1),
    (5,2,2);

Build your Query

First, if you just assume those FK's will set up the relationships for you, you have a problem

# BAD: This is cross product query, also called a cartesian join, DO NOT DO THIS!

SELECT b.title,b.yearpub, a.last_name, a.first_name, ba.author_order
FROM ybooks AS b, ybooks_authors ba, yauthors a;

as we did not specify how the tables are joined, it does a cross product query and in my case we had 300 results
To do it properly, we have to use joins, so each table you add after the first one you must do it via
JOIN nextablename nt ON (nt.keyname = ot.keyname)
where nt is the alias for nextablename and ot is for the other table that nextablename connects to in our design.
```
SELECT b.title,b.yearpub, a.last_name, a.first_name, ba.author_order
FROM ybooks AS b JOIN ybooks_authors ba ON (b.book_id= ba.book_id)
JOIN yauthors a ON (ba.author_id=a.author_id);
```

Now if we just want the first author

SELECT b.title,b.yearpub, a.last_name, a.first_name, ba.author_order
FROM ybooks AS b JOIN ybooks_authors ba ON (b.book_id= ba.book_id)
JOIN yauthors a ON (ba.author_id=a.author_id)
WHERE ba.author_order = 1;

If we want to search for books by a particular author:

SELECT b.title,b.yearpub, a.last_name, a.first_name, ba.author_order
FROM ybooks AS b JOIN ybooks_authors ba ON (b.book_id= ba.book_id)
JOIN yauthors a ON (ba.author_id=a.author_id)
WHERE a.last_name = "Orwell";

To find a book that has two specific authors (Lee and Hawking), you can not do an AND due to the join table, so you have to use OR , then group the results by b.title and specify the count of the title needs to be 2 (The title will show up once for each author, and GROUP BY b.title will combine identical titles, thereby making count(b.title) equal to 2 IF both authors are connected to that title.
```
SELECT b.title,b.yearpub
FROM ybooks AS b JOIN ybooks_authors ba ON (b.book_id= ba.book_id)
JOIN yauthors a ON (ba.author_id=a.author_id)
WHERE a.last_name = "Lee"
OR a.last_name = "Hawking"
GROUP BY b.title
HAVING count(b.title) = 2;
```

Page last updated on 20210125