Yes, that is what you asked for. You asked to do a NATURAL JOIN between two tables (Authors, Books) and then JOIN each one of those records with all the records from Years.
Did you perhaps mean: SELECT Name, Title, Year FROM Authors NATURAL JOIN Books NATURAL JOIN Years; However, even that will not give you what you want since you did not name the join column the same between the Books and Years table. The correct select would be: SELECT Name, Title, Year FROM Authors NATURAL JOIN Books, Years WHERE Books.YearID = Years.IDYear; or, without the syntactic sugar, SELECT Name, Title, Year FROM Authors, Books, Years WHERE Authors.AuthorID = Books.AuthorID AND Books.YearID = Years.IDYear; "NATURAL JOIN" is syntactic sugar for a "," in the from clause. It is replaced by a comma and all the columns (in the LHS and RHS tables) with the same name are added as equijoin conditions in the WHERE clause. Since you have no common column names between the Books and Years tables, you cannot use the NATURAL JOIN sugarcube (well, you can, but it does nothing since it has no conditions to add to the WHERE clause). > -----Original Message----- > From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] > On Behalf Of jose isaias cabrera > Sent: Wednesday, 21 December, 2016 01:05 > To: SQLite mailing list > Subject: [sqlite] More constraint questions: foreign_keys > > > With this code, > > -- Begin > PRAGMA foreign_keys=1; > BEGIN TRANSACTION; > DROP TABLE IF EXISTS Books; > DROP TABLE IF EXISTS Authors; > DROP TABLE IF EXISTS Years; > CREATE TABLE Authors(AuthorId INTEGER PRIMARY KEY, Name TEXT); > INSERT INTO Authors VALUES(1, 'Jane Austen'); > INSERT INTO Authors VALUES(2, 'Leo Tolstoy'); > INSERT INTO Authors VALUES(3, 'Joseph Heller'); > INSERT INTO Authors VALUES(4, 'Charles Dickens'); > > CREATE TABLE Years > ( > IDYear INTEGER PRIMARY KEY, > Year TEXT DEFAULT '1980' > ); > INSERT INTO Years VALUES(1,'1982'); > INSERT INTO Years VALUES(2,'1992'); > INSERT INTO Years VALUES(3,'2016'); > INSERT INTO Years(IDYear) VALUES(4); > > CREATE TABLE Books > ( > BookId INTEGER PRIMARY KEY, Title TEXT, AuthorId INTEGER, YearID > INTEGER, > FOREIGN KEY(AuthorId) REFERENCES Authors(AuthorId), > FOREIGN KEY(YearID) REFERENCES Years(IDYear) > ); > INSERT INTO Books VALUES(1,'Emma',1,1); > INSERT INTO Books VALUES(2,'War and Peace',2,1); > INSERT INTO Books VALUES(3,'Catch XII',3,2); > INSERT INTO Books VALUES(4,'David Copperfield',4,3); > INSERT INTO Books VALUES(5,'Good as Gold',3,4); > INSERT INTO Books VALUES(6,'Anna Karenia',2,3); > COMMIT; > > -- end > > If I do, > > SELECT Name, Title, Year FROM Authors NATURAL JOIN Books, Years; > > I get repeated data. But, what I want is, > > Jane Austen|Emma|1982 > Leo Tolstoy|War and Peace|1982 > Joseph Heller|Catch XII|1992 > Charles Dickens|David Copperfield|2016 > Joseph Heller|Good as Gold|1980 > Leo Tolstoy|Anna Karenia|2016 > > Any help would be greatly appreciated. Thanks. > > jos > _______________________________________________ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
