>>Donald, >> >>Thanks for your reply, its all a bit new to me, so i'm still trying to get my >>head round it and hopefully be able to explain myself better. >> >>I'm actually creating the unique ids, it is just a number. >> >>I now think my understanding of an attached database is wrong, my first >>thoughts were that a single SELECT command would query both databases, but >>now i believe i would have to use the following syntax to do a query on both >>databases: >> >>SELECT id, name FROM database1.main, database2.main WHERE name == %me% ORDER >>BY name >> >> >> >>Which would produce alphabetically sorted results from both attached tables? >>But because both the databases will have been created separately, they could >>have overlapping ids, which i need to avoid. >> >>To avoid overlapping ids i could manually get all the ids from database1 and >>(for arguments sake) add a '1' to the end and all the ids from database2 and >>add a '2' to the end. If the client then requested further information on id >>6731, i would know it was from database 1, strip that digit out and so get >>further information from id 673 database1. Doing it this way however means i >>have to alphabetically sort the results from the two tables, after the two >>queries. >> >>So i guess i was just wondering if this was all possible in a single query, >>or sqlite gave some indication of where the results were coming from so i >>could tag them for id mangling. >> >>I hope i've managed to explain myself better and not made it even worse! >> >>Thanks >>Andrew >> >> >Andrew,
>I think what you are after is more like this; > >select id * 10 + 1 as uid, name from main.sometable where name like '%me%' >union >select id * 10 + 2 as uid, name from database2.sometable where name like >'%me%' >order by name > >This will combine the results of the two queries, one on the sometable >of each database, and adjust the returned id so that the correct >database can be determined from the uid (by using the modulus operator >to get the database number, ie. uid % 10 is the database). This should >work as long as your id values are small enough. > >To return other data from a record specified by this uid value you can >use a case statement like this; > >select case :uid % 10 > when 1 then > select name, otherdata from main.sometable where id = :uid / 10 > when 2 then > select name, otherdata from database2.sometable where id = :uid / 10 > end; > >You can then bind the :uid value (or generate the sql string on the fly) >and get the requested data from the correct database. > >HTH >Dennis Cote Dennis, A ha! This seems perfect, one quick question, when you say "id values are small enough", are we talking about an integer size limit hard coded into sqlite? I mean with the *10 is the value of id ultimately limited to 6553? This may cause an issue, however if we are talking 32 bit then that won't be a problem. Thank you for your help, i'm continually impressed with how much you can do with an sql statement! Andrew
