Re: [sqlite] Multitable query question
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
Re: [sqlite] Multitable query question
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
Re: [sqlite] Multitable query question
Stephen Oberholtzer wrote: > > On Wed, May 28, 2008 at 11:15 PM, beejayzed <[EMAIL PROTECTED]> wrote: > >> >> I have two tables, one which has each visit(call) on a house logged, >> another >> which has each letter sent to a house logged. >> I would like to create a query which has two columns, Letters.Date and >> Calls.Date, for a specific HouseID. >> >> So how do I get the desired result? >> This would be: >> 26/5/08 15/5/08 >> 27/5/08 15/5/08 >> 28/5/08 15/5/08 >> 16/5/08 >> >> > Before anything else: Use ISO date formats, e.g. 2008-05-26 instead of > 26/5/08. Not only is it reasonably unambiguous (e.g. to me, 12/5/08 is > December 5th), but when you sort them as strings they also sort in > date/time > order. > > Secondly, glancing over your request, it seems that you have confused a > database table with the generic tabular data layout you might achieve > using > e.g. HTML. > Each row of a table AKA 'relation' contains related information. What you > seem to want, however, is completely different: You seem to actually want > two *different* sets of data, that have nothing to do with each other, and > display them in columns side-by-side. > > The proper way to do this is to have your application pull back all of the > letter dates in one query, then all of the visit dates in a second query, > and then piece them together for display purposes outside of SQL. > > > -- > -- 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 > > Oops.. I sent the reply to Stephen by mistake. Anyway, I'd created this behaviour in my program, but I was just curious whether it could be done in an sql query as well. I guess it's not really suitable for it. Thanks for the prompt replies. Also thanks for the suggestion about the dates. -- View this message in context: http://www.nabble.com/Multitable-query-question-tp17526832p17527752.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Multitable query question
On Wed, May 28, 2008 at 11:15 PM, beejayzed <[EMAIL PROTECTED]> wrote: > > I have two tables, one which has each visit(call) on a house logged, > another > which has each letter sent to a house logged. > I would like to create a query which has two columns, Letters.Date and > Calls.Date, for a specific HouseID. > > So how do I get the desired result? > This would be: > 26/5/08 15/5/08 > 27/5/08 15/5/08 > 28/5/08 15/5/08 > 16/5/08 > > Before anything else: Use ISO date formats, e.g. 2008-05-26 instead of 26/5/08. Not only is it reasonably unambiguous (e.g. to me, 12/5/08 is December 5th), but when you sort them as strings they also sort in date/time order. Secondly, glancing over your request, it seems that you have confused a database table with the generic tabular data layout you might achieve using e.g. HTML. Each row of a table AKA 'relation' contains related information. What you seem to want, however, is completely different: You seem to actually want two *different* sets of data, that have nothing to do with each other, and display them in columns side-by-side. The proper way to do this is to have your application pull back all of the letter dates in one query, then all of the visit dates in a second query, and then piece them together for display purposes outside of SQL. -- -- 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
Re: [sqlite] Multitable query question
"beejayzed" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > I have two tables, one which has each visit(call) on a house logged, > another which has each letter sent to a house logged. > I would like to create a query which has two columns, Letters.Date and > Calls.Date, for a specific HouseID. What for? Why do you want to put two completely unrelated values into the same row? This makes no sense from relational calculus standpoint, and that's why you have a hard time expressing such a beast in SQL. What are you trying to achieve? Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
