I read the replies in this thread on the sqlite mailing list.
It's true that the data in the two tables are not really related in the
relational sense, where one table contains a foreign key, which is a key in
another table. This implies a one to many relationship -- for example, many
calls relating to a given letter and many letters relating to a given house.
When the same foreign key (houseid) occurs in both tables, the best you can
hope for is to join all the values in eacj row of one table with all the values
each row of the other which share the same foreign key.
I thought about the problem of displaying "unrelated data" side by side in sql
queries. But first a quick and dirty solution, if all you want is a reference.
This also shows how some sorts of formatting can be done directly in a query.
The data appears in it's own column, but not side by side. Also, using the
standard date data type is really the *best* way to store dates.
.separator ""
.mode list
select "House 16: Letters Calls";
select "--------------------------";
select " ", date from letters where houseid = 16;
select " ", date from calls where houseid = 16;
To create a side by side report in SQLite, you would need to create a
relationship between the columns you want to display. This can be done by
creating temporary tables with auto-incrementing primary keys. Select the
unrelated data you want to display into these tables, as well as a number of
blank rows into each table so there can be columns of different lengths. Now,
you have a relationship between the data based on the auto-incrementing key
(id, for instance). Do a select from columns in these tables "where table1.id =
table2.id and table1.id = table3.id etc. limit 40" (or whatever is the maximum
length of your report. If somebody has found another way to do what you want,
at least this technique can be used when the data is completely unrelated and
you want a report which can be done completely in SQLite.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
