mysql - SQL 查询 INNER JOIN 4 个表

Question

我正在尝试显示已交付给成员(member)的已购图书列表。该 list 应显示成员(member)识别号码、名字和姓氏、地址、联系电话、书籍序列号、书籍标题、数量和交付日期。

我的表格在下面

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');`

到目前为止我已经尝试过但没有成功:

 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

任何帮助将不胜感激

Answer 1

试试这个:

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

请为所有日期使用日期格式。