This quetion is about mysql statement. Q.List all books information (postingID,

Question

This quetion is about mysql statement.

Q.List all books information (postingID, title, isbn, price ) that are not yet sold.

*salesTrans TABLE means that those books are sold.

I tried like this, but did not work.

SELECT S.postingID, B.bookTitle, B.ISBN, B.publisher, S.sellingPrice

FROM bookdb as B, salesdb as S, salestrans AS ST
WHERE B.postingID = S.postingID AND S.postingID = ST.postingID
AND S.postingID NOT IN (SELECT ST.postingID FROM salestrans ST)

Plz give me an answer and plz check if your statement works or not!!

Thanks.

Explanation / Answer

SELECT ST.postingID FROM salestrans ST; this will list all the postingIDs that are sold.

Now, you have to select the postingIDs that are not yet sold.

SELECT B.postingID FROM bookdb B WHERE B.postingID NOT IN (SELECT ST.postingID FROM salestrans ST); this will list all the postingIDs that are not yet sold.

But, now, all you need not only postingIDs, but also its title, isbn, and price. Ofcourse the title and ISBN are available in bookdb, but it seems price is not available there.

Assume that the selling prices are available in the table salesdb, along with their postingID. So, to retrieve the information as per your requirement.

SELECT B.postingID, B.bookTitle, B.ISBN, S.sellingPrice FROM bookdb B, salesdb S WHERE (B.postingID = S.postingID) AND (B.postingID NOT IN (SELECT ST.postingID FROM salestrans ST)); this will list all the required details of books that are not yet sold.

Hopefully this should work for you. If you're still facing any problem, just revert to me along with the complete schemas.

Related Questions

Navigate

• Browse (All)
• Browse T
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.