Multiple SQL joins
It will be something like this:
SELECT b.Title, b.Edition, b.Year, b.Pages, b.Rating, c.Category, p.Publisher, w.LastNameFROM Books b JOIN Categories_Book cb ON cb._ISBN = b._Books_ISBN JOIN Category c ON c._CategoryID = cb._Categories_Category_ID JOIN Publishers p ON p._PublisherID = b.PublisherID JOIN Writers_Books wb ON wb._Books_ISBN = b._ISBN JOIN Writer w ON w._WritersID = wb._Writers_WriterID
You use the join
statement to indicate which fields from table A map to table B. I'm using aliases here thats why you see Books b
the Books
table will be referred to as b
in the rest of the query. This makes for less typing.
FYI your naming convention is very strange, I would expect it to be more like this:
Book: ID, ISBN , BookTitle, Edition, Year, PublisherID, Pages, RatingCategory: ID, [Name]BookCategory: ID, CategoryID, BookIDPublisher: ID, [Name]Writer: ID, LastNameBookWriter: ID, WriterID, BookID
You can use something like this :
SELECT Books.BookTitle, Books.Edition, Books.Year, Books.Pages, Books.Rating, Categories.Category, Publishers.Publisher, Writers.LastNameFROM BooksINNER JOIN Categories_Books ON Categories_Books._Books_ISBN = Books._ISBNINNER JOIN Categories ON Categories._CategoryID = Categories_Books._Categories_Category_IDINNER JOIN Publishers ON Publishers._Publisherid = Books.PublisherIDINNER JOIN Writers_Books ON Writers_Books._Books_ISBN = Books._ISBNINNER JOIN Writers ON Writers.Writers_Books = _Writers_WriterID.
SELECT B.Title, B.Edition, B.Year, B.Pages, B.Rating --from Books, C.Category --from Categories, P.Publisher --from Publishers, W.LastName --from WritersFROM Books BJOIN Categories_Books CB ON B._ISBN = CB._Books_ISBNJOIN Categories_Books CB ON CB.__Categories_Category_ID = C._CategoryIDJOIN Publishers P ON B.PublisherID = P._PublisheridJOIN Writers_Books WB ON B._ISBN = WB._Books_ISBNJOIN Writers W ON WB._Writers_WriterID = W._WriterID