sql should be:
select b.Name, a.Title, c.Year
from Books a
left outer join Authors b on a.AuthorID = b.AuthorID
left outer join Years c on a.YearID = c.IDYear
I think your table Years is redundant.
Why store only one integer in table Years
as you could store directly into Books.
So I would have just Year in table Books.
Regards Radovan
jose isaias cabrera je 21.12.2016 ob 9:05 napisal:
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
