Andrew Gatt wrote:
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
