I don't know if FTS or a normal table will matter here but just normalize the
whole thing.
CREATE VIRTUAL TABLE virfts4 using fts4(id,level,value);
Your level can be CO, ST, CI, VI. Or 1,2,3,4. 1,2,3,4 would be a touch faster.
INSERT INTO virfts4 VALUES(1,'CO','country1');
INSERT INTO virfts4 VALUES(1,'ST','state1');
INSERT INTO virfts4 VALUES(1,'CI','city1');
INSERT INTO virfts4 VALUES(1,'VI','village1');
SELECT DISTINCT value FROM virfts4 WHERE level MATCH 'CO';
You can store as many levels as you want.
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Advanced GEOINT Solutions Operating Unit
Northrop Grumman Information Systems
________________________________
From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on
behalf of Durga D [durga.d...@gmail.com]
Sent: Tuesday, December 27, 2011 4:27 AM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] search
Thank you. I agree. It's correct.
I already have data base with /country/state/city/village format. Is it
possible to do that while virtual table creation time?
if yes, how?
in case, if I have 250 levels like this ex: file system. how to do this.
any idea?
Thanks in advance.
On Tue, Dec 27, 2011 at 3:38 PM, Kit <kit.sa...@gmail.com> wrote:
> 2011/12/27 Durga D <durga.d...@gmail.com>:
> > select * from virfts4 where residence match '/*'; -- dint work
> > how to get counties names from this db by using query?
>
> Normalize database to 1NF, e.g.
> CREATE VIRTUAL TABLE virfts4 using fts4(country, state, city, village,
> arrivtime, duration, imagelocation);
> INSERT INTO virfts4 VALUES ('country1','state1','city1','village1',
> 0730, 1500,'C');
>
> then use select:
> SELECT DISTINCT country FROM virfts4;
> --
> Kit
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
