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 sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users