SQL: JOIN 3 tables

1. Introduction

Using models.py, I created three tables:

from django.db import models

# Create your models here.

class Book(models.Model):

    title = models.CharField(max_length=200)

    authors = models.ManyToManyField('Author', through='Authored')

class Author(models.Model):

    name = models.CharField(max_length=200)

    books = models.ManyToManyField('Book', through='Authored')

# this is through table (junction table for many-to-many)

class Authored(models.Model):    

    book = models.ForeignKey(Book, on_delete=models.CASCADE)

    author = models.ForeignKey(Author, on_delete=models.CASCADE)

 

I inserted some data, then I went to dbshell:

python manage.py dbshell

 

I did:

sqlite> select * from bookmany_author;

1|Fontichiaro

2|Severance

sqlite> select * from bookmany_book;

1|Raspberry

2|Networking

sqlite> select * bookmany_authored;

Error: in prepare, near "bookmany_authored": syntax error (1)

sqlite> select * from bookmany_authored;

1|2|2

4|1|1

5|2|1

sqlite> .mode column

sqlite> select * from bookmany_authored;

id  author_id  book_id

--  ---------  -------

1   2          2      

4   1          1      

5   2          1      

 

Then, I wondered how to get authors from a book.

 

2. Q and A

The following shows the tables.  Using SQL, how can I get, for example, authors of a book?

sqlite> select * from bookmany_author;

1|Fontichiaro

2|Severance

sqlite> select * from bookmany_book;

1|Raspberry

2|Networking

sqlite> select * bookmany_authored;

Error: in prepare, near "bookmany_authored": syntax error (1)

sqlite> select * from bookmany_authored;

1|2|2

4|1|1

5|2|1

sqlite> .mode column

sqlite> select * from bookmany_authored;

id  author_id  book_id

--  ---------  -------

1   2          2      

4   1          1      

5   2          1      

 

 

To get the authors of a specific book using SQL (e.g., authors of the book "Raspberry"), you'll need to join the three tables: bookmany_book, bookmany_authored, and bookmany_author.

---

✅ Example: Authors of the book "Raspberry"

---

🧠 Explanation:

• bookmany_authored is a junction table that connects authors and books.

• You're joining:

  • author.id ↔ authored.author_id

  • book.id ↔ authored.book_id

• Then you filter by book.title = 'Raspberry'.

---

📘 Output you should get:

Based on your data:

 

Let me know if you want the reverse (books written by a specific author).

 

3. I tried some queries.