CODEWITHSUNDEEP
sqldatabase
Abdul Rehman
Abdul Rehman

Reputation: 187

Difficulty in Creating Division Operation Between Two tables

CREATE TABLE BARROW
(
    ID INT PRIMARY KEY,
    STUDENT_ID INT ,
    BOOK_ID INT,
)

INSERT INTO BARROW (ID,STUDENT_ID,BOOK_ID)VALUES (1,10010,00025)
INSERT INTO BARROW (ID,STUDENT_ID,BOOK_ID)VALUES (2,11094,00107)
INSERT INTO BARROW (ID,STUDENT_ID,BOOK_ID)VALUES (3,11094,00005)
INSERT INTO BARROW (ID,STUDENT_ID,BOOK_ID)VALUES (4,10121,00107)
INSERT INTO BARROW (ID,STUDENT_ID,BOOK_ID)VALUES (5,10020,00001)
INSERT INTO BARROW (ID,STUDENT_ID,BOOK_ID)VALUES (6,10020,00005)

CREATE TABLE BOOK
(
    ID INT PRIMARY KEY ,
    TITLE VARCHAR(100),
    AUTHOR VARCHAR(100),
    PUBLISHER VARCHAR(100),
    PUB_YEAR INT,
    ISBN_NO BIGINT,
)


INSERT INTO BOOK (ID,TITLE,AUTHOR,PUBLISHER,PUB_YEAR,ISBN_NO)VALUES (00001,'An Intoduction to Database Systems','A.k.Alien','McGrawHill',2001,74895841)
INSERT INTO BOOK (ID,TITLE,AUTHOR,PUBLISHER,PUB_YEAR,ISBN_NO)VALUES (00005,'An Intoduction to Relational Database','R.Torlone','Addison Wesley',2003,58663571)
INSERT INTO BOOK (ID,TITLE,AUTHOR,PUBLISHER,PUB_YEAR,ISBN_NO)VALUES (00025,'Prgramming with visual basic','N.B.Grag','Prentice Hall',1999,851447356)
INSERT INTO BOOK (ID,TITLE,AUTHOR,PUBLISHER,PUB_YEAR,ISBN_NO)VALUES (00107,'Mathematics For Engeenering','T.Date','Addison Wesley',2000,956482550)
INSERT INTO BOOK (ID,TITLE,AUTHOR,PUBLISHER,PUB_YEAR,ISBN_NO)VALUES (03567,'Fundamentals of Programming','W.L.Loure','Prentice Hall',2004,1551648)
INSERT INTO BOOK (ID,TITLE,AUTHOR,PUBLISHER,PUB_YEAR,ISBN_NO)VALUES (00267,'Engeenering Mathematics','W.C.Evans','Oxford',2005,4478568)
INSERT INTO BOOK (ID,TITLE,AUTHOR,PUBLISHER,PUB_YEAR,ISBN_NO)VALUES (00251,'Engeenering Mathematics','D.Woods','Prentice Hall',2003,79212655)

These are my tables and values, I want to fetch the records of all the student_ids which have issued books with publisher name as 'Addison Wesley'

The result I want to fetch: 

( studentID,bookID(Borrow)) ÷ ( (bookID)( id ( publisher = Addison Wesley (Book))))

I want to perform division operation between two tables and want to get student ids from that:

SELECT STUDENT_ID, BOOK_ID 
FROM BARROW 

DIV

SELECT ID AS B 
FROM BOOK 
WHERE PUBLISHER = 'Addison Wesley'

From this image result I want to get all the students ids by performing division operation...

image

Upvotes: 0

Views: 33

Answers (1)

Nick
Nick

Reputation: 147166

What you want is a JOIN, where we connect each student to the books they have issued and then filter that based on the PUBLISHER field:

SELECT DISTINCT STUDENT_ID
FROM BARROW BA
JOIN BOOK BK ON BK.ID = BA.BOOK_ID
WHERE BK.PUBLISHER = 'Addison Wesley'

Output:

STUDENT_ID
11094
10121
10020

Demo on SQLFiddle

We use DISTINCT to avoid duplication of rows where one student has issued more than one book published by Addison Wesley

Upvotes: 1

Related Questions