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
