Hi to all,
I'm looking for a query on the following schema:
PRAGMA foreign_keys=ON;
BEGIN TRANSACTION;
CREATE TABLE subj
(
sub_id INTEGER PRIMARY KEY, -- 00
sub_descr TEXT DEFAULT NULL -- 01
);
INSERT INTO "subj" VALUES(1,'Paul');
INSERT INTO "subj" VALUES(2,'John');
CREATE TABLE itx
(
itx_id INTEGER PRIMARY KEY, -- 00
itx_descr TEXT DEFAULT NULL -- 01
);
INSERT INTO "itx" VALUES(1,'Box');
INSERT INTO "itx" VALUES(2,'Pen');
INSERT INTO "itx" VALUES(3,'Letter');
CREATE TABLE appx
(
app_ref_itx INTEGER DEFAULT NULL -- 00
REFERENCES itx(itx_id) ON UPDATE CASCADE ON DELETE RESTRICT,
app_ref_sub INTEGER DEFAULT NULL -- 01
REFERENCES subj(sub_id) ON UPDATE CASCADE ON DELETE RESTRICT,
app_load INTEGER NOT NULL DEFAULT 0, -- 02
UNIQUE(app_ref_itx, app_ref_sub)
);
INSERT INTO "appx" VALUES(1,1,3);
INSERT INTO "appx" VALUES(3,2,4);
COMMIT;
table appx stores item's(table itx) quantity load for each user (table
subj). I'd to know how many items each user has:
Paul|Box|3
Paul|Letter|0
Paul|Pen|0
John|Box|0
John|Letter|4
John|Pen|0
and the above is the result what I aim to!!!
I tried:
select sub_descr, itx_descr, app_load from subj
left outer join appx on sub_id = app_ref_sub
left outer join itx on app_ref_itx = itx_id;
but it returns only:
Paul|Box|3
John|Letter|4
Thanks in advance, Danilo
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
