Book table:

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 table:

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 table:

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 table:

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.

CodePudding user response：

First list all the fields you're interested in, then choose a starting table and add the joins one by one with just the conditions to connect the tables. Last add a WHERE clause that filters out the items you're interested in. In this case, you'll end up with something like;

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

A simple DBfiddle to test with.