I need to execute a query To retrieve data from multiple tables but I'm rather confused on how to do it all at once.
Books: _ISBN , BookTitle, Edition, Year, PublisherID, Pages, Rating
Categories: _CategoryID, Category
Categories_Books: _Categories_Category_ID, _Books_ISBN
Publishers: _Publisherid, Publisher
Writers: _WriterID, LastName
Writers_Books: _Writers_WriterID, _Books_ISBN
Categories_Books
and Writers_Books
are the intermediate tables to help me implement many to many relationships between the tables.
I need a single query with multiple joins to select:
SELECT
B.Title, B.Edition, B.Year, B.Pages, B.Rating --from Books
, C.Category --from Categories
, P.Publisher --from Publishers
, W.LastName --from Writers
FROM Books B
JOIN Categories_Books CB ON B._ISBN = CB._Books_ISBN
JOIN Categories_Books CB ON CB.__Categories_Category_ID = C._CategoryID
JOIN Publishers P ON B.PublisherID = P._Publisherid
JOIN Writers_Books WB ON B._ISBN = WB._Books_ISBN
JOIN Writers W ON WB._Writers_WriterID = W._WriterID
You can use something like this :
SELECT
Books.BookTitle,
Books.Edition,
Books.Year,
Books.Pages,
Books.Rating,
Categories.Category,
Publishers.Publisher,
Writers.LastName
FROM Books
INNER JOIN Categories_Books ON Categories_Books._Books_ISBN = Books._ISBN
INNER JOIN Categories ON Categories._CategoryID = Categories_Books._Categories_Category_ID
INNER JOIN Publishers ON Publishers._Publisherid = Books.PublisherID
INNER JOIN Writers_Books ON Writers_Books._Books_ISBN = Books._ISBN
INNER JOIN Writers ON Writers.Writers_Books = _Writers_WriterID.
Source: Stackoverflow.com