CODEWITHSUNDEEP
sqlsqlite
Mina Medhat
Mina Medhat

Reputation: 25

SQLite, Many to many relations

I have the following database schema:

CREATE TABLE "Student" (
"Student_ID" INTEGER PRIMARY KEY  NOT NULL , 
"First_Name" CHAR, "Last_Name" CHAR, "Course" CHAR, 
"Year" INTEGER, "Semester" INTEGER)

CREATE TABLE "AddBook" ("Book_ID" INTEGER PRIMARY KEY  NOT NULL , 
"Name" CHAR, "Edition" INTEGER, "Publisher" CHAR, "Price" INTEGER,
 "Page" INTEGER)

I'm making g a table for borrowing books name Issue and i put in it the "Student_ID" and "Book_ID" and the date of borrowing this is the database schema:

CREATE TABLE "Issue" ("Student_ID" INTEGER PRIMARY KEY  NOT NULL ,
"Book_ID" INTEGER NOT NULL ,"Date" INTEGER DEFAULT (null) )

how to show any borrowed books by but showing the all the student info and all of the book info and the date

Upvotes: 0

Views: 69

Answers (2)

CL.
CL.

Reputation: 180020

You have to join the three tables together:

SELECT *
FROM Issue
JOIN Student USING (Student_ID)
JOIN AddBook USING (Book_ID);

Upvotes: 0

Filipe Martins
Filipe Martins

Reputation: 608

You can do like this:

SELECT s.*, b.*, i.date
FROM Issue I
INNER JOIN Student s ON (i.Student_ID = s.Student_ID)
INNER JOIN AddBook b ON (i.Book_ID = b.Book_ID)

INNER JOIN will find the match student and the match book of your Issue table using the ID that you provided.

Upvotes: 2

Related Questions