列出訂購的尚未交付給會員的書籍

Book 桌子：

CREATE TABLE Book  
(
    BookID nvarchar(50) NOT NULL PRIMARY KEY,    
    BookName nvarchar(50),      
    SerialNumber nvarchar(50),    
    CategoryID nvarchar(50) 
        FOREIGN KEY REFERENCES Category(CategoryID), 
    PublisherID NVARCHAR(50) 
        FOREIGN KEY REFERENCES Publisher(PublisherID), 
    Unit_Price nvarchar(50), 
    Quantity int
); 

INSERT INTO Book 
VALUES ('B001 ','Spiderman ','X111', 'C001 ', 'P001','1','6'), 
       ('B002 ','Superman ','X112 ', 'C001', 'P001','2','5'),
       ('B003  ','Love Is Love','X113 ', 'C002', 'P002 ','3','4'),
       ('B004  ','The Witcher ','X114 ', 'C004', 'P003','4','3'),
       ('B005 ','Intro to Database ','X115 ', 'C005', 'P003','5','2'),
       ('B006 ','DataScience','X116 ', 'C005', 'P003','6','1');

Member 桌子：

CREATE TABLE Member 
(
    MemberID nvarchar(50) NOT NULL PRIMARY KEY, 
    Name nvarchar(50), 
    Address nvarchar(50), 
    Identification_number nvarchar(50), 
    Contact_Number nvarchar(50),
    Gender nvarchar(50)
);

INSERT INTO Member 
VALUES ('M001','ALi','KL', '8001', '011-11111','Male'), 
       ('M002 ','Ahmed ','Ipoh', '8002', '011-11112','Male'),
       ('M003 ','Farah ','Johor', '8003', '011-11113','Female'),
       ('M004 ','Charlie ','Penang', '8004', '011-11114','Male'),
       ('M005 ','Khalid ','Penang', '8005', '011-11115','Male'),
       ('M006 ','Justin ','Subang', '8006', '011-11116','Male');

MemberOrder 桌子：

CREATE TABLE MemberOrder  
(
    MemberOrderID nvarchar(50) NOT NULL PRIMARY KEY, 
    MemberID nvarchar(50) 
        FOREIGN KEY REFERENCES Member(MemberID), 
    Delivery_Status nvarchar(50), 
    Order_Date date, 
    Total_Price float(50)
); 

INSERT INTO MemberOrder 
VALUES ('O001  ','M001 ','Delivered ', '25 Dec 2022 ', '5'), 
       ('O002  ','M001 ','In Transit ', '10 Jan 2022', '6'),
       ('O003   ','M002 ','Delivered  ', '13 Jan 2022 ', '12'),
       ('O004   ','M004  ','In Transit ', '13 Jan 2022', '20'),
       ('O005  ','M006','Delivered  ', '14 Jan 2022 ', '4'),
       ('O006  ','M001','In transit ', '14 Jan 2022 ', '3');

MemberOrderContent 桌子：

CREATE TABLE MemberOrderContents 
(
    MemberOrderContentsID nvarchar(50) NOT NULL PRIMARY KEY, 
    MemberOrderID nvarchar(50) 
        FOREIGN KEY REFERENCES MemberOrder(MemberOrderID), 
    BookID nvarchar(50) 
        FOREIGN KEY REFERENCES Book(BookID),  
    Quantity nvarchar(50), 
    Total nvarchar(50)
); 

INSERT INTO MemberOrderContents 
VALUES ('MOC001   ','O001  ','B001  ', '1 ', '1'), 
       ('MOC002   ','O001  ','B002 ', '2', '4'),
       ('MOC003    ','O002  ','B002 ', '3 ', '6'),
       ('MOC004    ','O003   ','B003 ', '4', '12'),
       ('MOC005   ','O004 ','B004  ', '5 ', '20'),
       ('MOC006   ','O005 ','B001  ', '1 ', '1'),
       ('MOC007   ','O005 ','B003   ', '1 ', '3'),
       ('MOC008   ','O006 ','B003   ', '1 ', '3');

The question asks me to list the ordered books that have not been delivered to members. The list should show member identification number, address, contact number, book serial number, book title, quantity, date and status of delivery.

From what I understand, I will need to use join to combine the columns from these 4 tables. I have successfully done the questions where they asked me to join two tables and now I'm a bit confused when it comes to 4 tables.

Your help will be much appreciated.

uj5u.com熱心網友回復：

首先列出你感興趣的所有欄位，然后選擇一個起始表，并以連接表的條件逐個添加連接。最后添加一個 WHERE 子句，過濾掉你感興趣的專案。在這種情況下，你最終會得到類似的東西；

SELECT 
   Member.MemberID, Member.Address, Member.Contact_Number, 
   Book.SerialNumber, Book.BookName, MemberOrderContents.Quantity, 
   MemberOrder.Order_Date, MemberOrder.Delivery_Status
FROM
   MemberOrder
JOIN
   Member ON MemberOrder.MemberID = Member.MemberID
JOIN 
   MemberOrderContents ON MemberOrder.MemberOrderID = MemberOrderContents.MemberOrderID
JOIN 
   Book ON MemberOrderContents.BookID = Book.BookID
WHERE 
   MemberOrder.Delivery_Status <> 'Delivered'
ORDER BY
   Order_Date, MemberID

一個簡單的 DBfiddle 進行測驗。

標籤：

上一篇：在此背景關系中不允許使用名稱“Y”。有效運算式是常量常量運算式

下一篇：使用帶有orderby的多個條件查詢