Thanks for the help, Dennis. I created a bug with ticket number 1035 for anyone interested in following up on this.
Thanks! Bob -----Original Message----- From: Dennis Cote [mailto:[EMAIL PROTECTED] Sent: Thursday, December 16, 2004 2:01 PM To: [EMAIL PROTECTED] Subject: Re: [sqlite] Union queries with sub-select tables with limits returns no results Bob Dankert wrote: > I am trying to union two queries with each query having a sub-select > defining the table, but I consistenly get no results if I try to limit > the sub-query. Here is my query, assuming the table a and table > contain one column filled with integers. > > Select * from (select * from a limit 3) as a; <-- this returns 3 > results > Select * from (select * from b limit 3) as b; <-- this returns 3 > results > > Select * from (select * from a limit 3) as a union select * from > (select * from b limit 3) as b; <-- this returns 0 results > > If I get rid of the limits, everything is returned from both tables as > it should be: > Select * from (select * from a) as a union select * from (select * > from b) as b; > > Unfortunately, I need to limit the results in individual queries which > are being unioned together. Since SQLite does not support limiting > individual queries in a union, I was hoping to use sub-selects for the > tables and limit these. Does anyone have any suggestions as to what I > can do for this? > > Thanks, > > Bob Dankert Bob, As a work around you can create temp tables from the two sub-selects and use a union select to combine them, or create a temp table with the output of the first sub-select and then insert the result of the second sub-select into the temp table and then dump that table. create temp table t1 as select * from a limit 3; create temp table t2 as select * from b limit 3; select * from t1 union select * from t2; or create temp table u as select * from a limit 3; insert into u select * from b limit 3; select * from u; I hope this helps.
