Thanks, Keith. This is exactly what I was looking for. Explanations with
examples and variations. The original tutorial that I was following did not
have these great insights. And yes, going to the sqlite.org site and
reading about the command there is the idea, but I enjoy examples and great
explanations like yours. Thanks so much. I really appreciate your input.
josé
Keith Medcalf wrote...
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
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
