INNER JOIN problem (MySQL Error Code: 1054)-CodePudding

I'm trying my hand with mySQL for the first time for a home-learning task and, after creating a Books, Users and Loaned tables (it's a library mock-up), one of the assignments asks that I combine all users that have loaned a book and all books that have been loaned by every user. I tried it but it gives me the aforementioned error (probably my grasp of mySQL is at fault) and I couldn't find anything online that could help me (or that I could understand, I must admit). Please find below what I used up until now.

Creating the database and table "Books":

CREATE DATABASE library;
CREATE TABLE Books (
    title VARCHAR(100) NOT NULL,
    author_1 VARCHAR(30) NOT NULL,
    author_2 VARCHAR(30), -- all these other "author" fields are for books with more than one author, but they can be NULL
    author_3 VARCHAR(30),
    author_4 VARCHAR(30),
    author_5 VARCHAR(30),
    author_6 VARCHAR(30),
    author_7 VARCHAR(30),
    author_8 VARCHAR(30),
    author_9 VARCHAR(30),
    author_10 VARCHAR(30),
    ISBN BIGINT NOT NULL, -- an ISBN can be between 10 and 13 num long, so not enough for an INT
    PRIMARY KEY(ISBN),
    stock INT NOT NULL
);
INSERT INTO Books VALUES ("Call of Cthulhu: Fantasy Roleplaying in the Worlds of HP Lovecraft", "Sandy Petersen", NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 9780933635586, 2); -- Since I included the possibility of having multiple authors, I'm forced to write NULL for the empty fields, as I don't know how else to manage this (as of the time of me writing this)
INSERT INTO Books VALUES ("Vampire: The Masquerade", "Phil Brucato", "Tom Dowd", "Mark Rein-Hagen", "Richard E. Dansky", NULL, NULL, NULL, NULL, NULL, NULL, 9781565042490, 4);
INSERT INTO Books VALUES ("IT", "Stephen King", NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 9780670813025, 27);
INSERT INTO Books VALUES ("Elantris", "Brandon Sanderson", NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 9780765311771, 12);
INSERT INTO Books VALUES ("Watchmen", "Alan Moore", "Dave Gibson", NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 9781852860240, 15);
INSERT INTO Books VALUES ("Supergod", "Warren Ellis", "Garrie Gastonny", NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 9781592911004, 6);
INSERT INTO Books VALUES ("Necronomicon", "H. R. Giger", NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,  9780905664064, 8);
INSERT INTO Books VALUES ("The Fantastic Art of Frank Frazetta", "Frank Frazetta", "Betty Ballantine",  NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 9780684144160, 1);
INSERT INTO Books VALUES ("Trigun", "Yasuhiro Nightow",  NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 9783551767011, 2);
INSERT INTO Books VALUES ("Ghost in the Shell", "Masamune Shirow",  NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 9781569710814, 5);
COMMIT;

Creating table "Users":

USE library;
CREATE TABLE Users (
    user_id MEDIUMINT AUTO_INCREMENT NOT NULL, -- MEDIUMINT because I don't think a library is going to have more than 8388607 users but it might have more than 32767 (SMALLINT)
    PRIMARY KEY(user_id),
    First_Name VARCHAR(15) NOT NULL,
    Last_Name VARCHAR(15) NOT NULL,
    Contact_Number VARCHAR(32) NOT NULL, -- Longest telephone number possible should be at max 15 num long, then there are the  , the country code, the area code and the possibility of other char like () - and so on
    City VARCHAR(85) NOT NULL, -- 85 is the longest name recorded for any place
    Address_1 VARCHAR(50) NOT NULL, -- Address shouldn't be more than 35-40, but just in case
    Address_2 VARCHAR(50), -- can be NULL, as it's not necessary to fill
    County VARCHAR(30) NOT NULL, -- longest county name in the UK is "Cambridgeshire and Isle of Ely", a total of 30 char
    Postcode CHAR(7) NOT NULL -- I chose a CHAR because postcodes a pretty consistent, being 5 to 7 char, so maybe it could save up memory?
);
INSERT INTO Users VALUES (4, "Bruce", "Wayne", 07893475629, "Gotham City", "1007, Mountain Drive", "Batcave", "Gotham", "NG8 2AE");
INSERT INTO Users VALUES (6, "Tony", "Stark", 09023853948, "Point Dume", "10880, Malibu Point", NULL, "Malibu", "MRVL616");
INSERT INTO Users VALUES (49, "Jessica", "Fletcher", 93829774, "Cabot Cove", "698, CAndlewood Lane", NULL, "Maine", "FN39F");
INSERT INTO Users VALUES (287, "Sherlock", "Holmes", 2984723987, "London", "221B, Baker Street", NULL, "Greater London", "OFN38");
INSERT INTO Users VALUES (2397, "Herman", "Munster", 2593528955, "Mockingbird Heights", "1313, Mockingbird Lane", NULL, "Los Angeles", "NF93FN");
INSERT INTO Users VALUES (29374, "Phileas", "Fogg", 234829384, "London", "7, Savile Row", "Burlington Gardens", "Greater London", "984NF");
INSERT INTO Users VALUES (37, "Spongebob", "Squarepants", 293829493, "Bikini Bottom", "124, Conch Street", NULL, "Pacific Ocean", "46BF9");
INSERT INTO Users VALUES (7, "Sweeny", "Todd", 0923492734, "London", "186, Fleet Street", NULL, "Greater London", "8DK92");
INSERT INTO Users VALUES (2, "Homer", "Simpson", 0234475923, "Springfield", "742, Evergreen Terrace", NULL, "Maine", "PE02SN");
INSERT INTO Users VALUES (73387, "Harry", "Potter", 02387426, "Little Whinging", "4, Privet Drive", "The Cupboard unda the Stairs", "Surrey", "FO9FH");
COMMIT;

Create table "Loaned":

USE library;

CREATE TABLE Loaned (
    user_id_loaned MEDIUMINT AUTO_INCREMENT NOT NULL,
    ISBN_loaned BIGINT NOT NULL,
    FOREIGN KEY (user_id_loaned) REFERENCES Users(user_id),
    FOREIGN KEY (ISBN_loaned) REFERENCES Books(ISBN),    
    date_lent DATE NOT NULL,
    due_date DATE NOT NULL
);
INSERT INTO Loaned VALUES (49, 9780684144160, "1991-03-29", "1991-05-29");
INSERT INTO Loaned VALUES (287, 9780933635586, "1827-08-17", "1827-10-17");
INSERT INTO Loaned VALUES (2397, 9780670813025, "2022-05-10", "2022-07-10");
INSERT INTO Loaned VALUES (37, 9780670813025, "2022-04-19", "2022-06-19");
INSERT INTO Loaned VALUES (2397, 9780905664064, "2022-03-21", "2022-05-21");
INSERT INTO Loaned VALUES (29374, 9781592911004, "2022-05-16", "2022-07-16");
COMMIT;

And finally, the part where I'm stuck:

SELECT * FROM Users; -- All Users
SELECT * FROM Books; -- All Books
SELECT Loaned.user_id_loaned, Users.First_Name, Users.Last_Name, Books.title, Loaned.ISBN_Loaned FROM ((Users u INNER JOIN Loaned l ON u.user_id = l.user_id_loaned) INNER JOIN Books b ON b.ISBN = l.ISBN_loaned);

CodePudding user response：

The problem is, when you assign an alias to a table you should use the alias on the select statement, not the real table name.

SELECT l.user_id_loaned, 
       u.First_Name, 
       u.Last_Name, 
       b.title, 
       l.ISBN_Loaned 
FROM Users u 
INNER JOIN Loaned l ON u.user_id = l.user_id_loaned
INNER JOIN Books b ON b.ISBN = l.ISBN_loaned

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=c4137f9c712934bf60074657f72701f4