On Thu, May 29, 2008 at 1:09 AM, <[EMAIL PROTECTED]> wrote: > I've implemented this behaviour in my program. I was just curious whether > it was possible in sql when I learnt about the Left Join (as there are more > values in one column than the other). > > I guess it isn't or is not really the proper use of the database. Thanks > very much for the prompt replies though. > I'll also have to sort out the date entry bit.
Ah, okay :) Cool. Just for the heck of it, I decided to attempt what you were going for. I do not, repeat, I do NOT recommend using this technique. I'm just proving what SQLite is capable of if you put a sufficiently twisted mind towards it :) CREATE TABLE Calls (id integer primary key, houseId integer, date date); CREATE TABLE Letters (id integer primary key, houseId integer, date date); CREATE TABLE callSeq (sequence integer primary key, date date); CREATE TABLE letterSeq (sequence integer primary key, date date); CREATE INDEX Calls_HouseId on Calls(houseId); CREATE INDEX Letters_HouseId on Letters(houseId); sqlite> .dump BEGIN TRANSACTION; CREATE TABLE Calls (id integer primary key, houseId integer, date date); INSERT INTO "Calls" VALUES(1,1,'2008-05-15'); INSERT INTO "Calls" VALUES(9,1,'2008-05-28'); INSERT INTO "Calls" VALUES(10,1,'2008-05-28'); INSERT INTO "Calls" VALUES(24,16,'2008-05-15'); INSERT INTO "Calls" VALUES(27,16,'2008-05-15'); INSERT INTO "Calls" VALUES(31,16,'2008-05-15'); CREATE TABLE Letters (id integer primary key, houseId integer, date date); INSERT INTO "Letters" VALUES(1,16,'2008-05-26'); INSERT INTO "Letters" VALUES(3,16,'2008-05-27'); INSERT INTO "Letters" VALUES(4,16,'2008-05-28'); INSERT INTO "Letters" VALUES(7,16,'2008-05-16'); CREATE INDEX Calls_HouseId on Calls(houseId); CREATE INDEX Letters_HouseId on Letters(houseId); COMMIT; And here's the magic: create temporary table letterSeq (sequence integer primary key, date date); create temporary table callSeq (sequence integer primary key, date date); insert into letterSeq (date) select date from Letters where houseId=16 order by date desc; insert into callSeq (date) select date from Calls where houseId=16 order by date desc; insert into callSeq (date) select NULL from Letters where (select count(*) from callSeq) < (select count(*) from letterSeq); select l.date as LetterDate, c.date as CallDate from callSeq c join letterSeq l on c.sequence = l.sequence order by c.sequence; Let the IOSQC (International Obfuscated SQLite Querying Contest) begin! -- -- Stevie-O Real programmers use COPY CON PROGRAM.EXE _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users