Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Multiple SQL joins

Tags:

sql

join

sqlite

People also ask

Can we use multiple JOINs in SQL?

Multiple joins can be described as a query containing joins of the same or different types used more than once, thus giving them the ability to combine multiple tables. For this article we will first create a database geeks and then create three tables in it and then run our queries on those tables.

Can we join 4 tables in SQL?

Using JOIN in SQL doesn't mean you can only join two tables. You can join 3, 4, or even more! The possibilities are limitless.

Can I join more than 2 tables in SQL?

In SQL Server, you can join more than two tables in either of two ways: by using a nested JOIN , or by using a WHERE clause. Joins are always done pair-wise.


It will be something like this:

SELECT b.Title, b.Edition, b.Year, b.Pages, b.Rating, c.Category, p.Publisher, w.LastName
FROM
    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, Rating
Category: ID, [Name]
BookCategory: ID, CategoryID, BookID
Publisher: ID, [Name]
Writer: ID, LastName
BookWriter: 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.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.

 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