SQL Query INNER JOIN 4 Tables

Question

i'm trying to show A list of purchased books that have been delivered to members. The list should show member identification number, first name and last name, address, contact number, book serial number, book title, quantity and delivery date.

My Tables are below

CREATE TABLE Members
            (
                MemberID nvarchar(50) NOT NULL PRIMARY KEY, 
                MemberName nvarchar(50) NOT NULL, 
                MemberAddress nvarchar(50),
                MemberContact int,
                MemberAge int,
                RegistrationDate nvarchar(50) 
            );


            --Inserting Values into Members' Table--
            INSERT INTO Members(MemberID, MemberName, MemberAddress, MemberContact, MemberAge,RegistrationDate)
            VALUES ('MEM01', 'Muhammed Abdul', 'Damansara','01121565987', '34', '20/02/2017');

        CREATE TABLE Book
        ( 
            BookID nvarchar(50) NOT NULL PRIMARY KEY, 
            BookName nvarchar(50) NOT NULL,
            BookCategory nvarchar(50),
            BookPrice1 nvarchar(50),
            Author nvarchar(50),
            PublisherID nvarchar(50) NOT NULL FOREIGN KEY REFERENCES Publisher(PublisherID)

        );
        --Inserting Values into Book Table--
        INSERT INTO Book(BookID, BookName, BookCategory, BookPrice1, Author, PublisherID)
        VALUES ('B01', 'Harry Potter', 'Fantasy','70', 'Rowling J.K','PB01');

        CREATE TABLE Orders_Bookstore
        (
            OrderID nvarchar(50) NOT NULL PRIMARY KEY,
            BookID nvarchar(50) NOT NULL FOREIGN KEY REFERENCES Book(BookID),
            OrderDate nvarchar(50),
            OrderQuantity int,
            OrderTotal int,
            MemberID nvarchar(50) NOT NULL FOREIGN KEY REFERENCES Members(MemberID),
        );
        --Inserting Values into Boookstore Orders table--
        INSERT INTO Orders_Bookstore(OrderID, BookID, OrderDate, OrderQuantity, OrderTotal, MemberID)
        VALUES ('ORD1','B01', '04/06/2017', '3' ,'210', 'MEM01');

        CREATE TABLE Receipt
        (
            ReceiptID nvarchar(50) NOT NULL PRIMARY KEY,
            CartNum nvarchar(50) FOREIGN KEY REFERENCES ShoppingCart(CartNum),
            DateOfDelivery nvarchar(50),
            DeliveryStatus nvarchar(50),
            MemberID nvarchar(50) FOREIGN KEY REFERENCES Members(MemberID)
        );

        --Inserting Values INTO Receipt table--
        INSERT INTO Receipt(ReceiptID, CartNum, DateOfDelivery,DeliveryStatus)
        VALUES ('REC1', 'CART1', '08/15/2017', 'Delivered');`

What i've tried so far but no luck:

 SELECT MemberID, MemberName, MemberAddress, MemberContact, BookID, BookName, 
   OrderQuantity, DeliveryStatus, DateOfDelivery FROM Members
   INNER JOIN Orders_Bookstore ON Members.MemberID=Orders_Bookstore.MemberID
   INNER JOIN Members ON Orders_Bookstore.MemberID=Members.MemberID
   INNER JOIN Book ON Orders_Bookstore.BookID=Book.BookID
   INNER JOIN Receipt ON Receipt.MemberID=Members.MemberID

Any help would be appreciated

Answer 1

try this :

SELECT 
m.MemberID, m.MemberName, m.MemberAddress, m.MemberContact, 
b.BookID, b.BookName, 
ob.OrderQuantity, 
r.DeliveryStatus, r.DateOfDelivery   
   FROM Members m 
   INNER JOIN Orders_Bookstore ob ON m.MemberID=ob.MemberID
   INNER JOIN Book b ON ob.BookID=b.BookID
   INNER JOIN Receipt r ON r.MemberID=m.MemberID

Kindly use date format for all dates.

Answer 2

A four table join requires 3 INNER JOINS. You are using 4, meaning you somewhere use a table twice. Try:

SELECT MemberID
    ,MemberName
    ,MemberAddress
    ,MemberContact
    ,Book.BookID
    ,BookName
    ,OrderQuantity
    ,DeliveryStatus
    ,DateOfDelivery
FROM Members m1
INNER JOIN Orders_Bookstore ON m1.MemberID = Orders_Bookstore.MemberID
INNER JOIN Book ON Orders_Bookstore.BookID = Book.BookID
INNER JOIN Receipt ON Receipt.MemberID = m1.MemberID