On Mon, Feb 23, 2009 at 5:15 AM, Yuzem <naujnit...@gmail.com> wrote: > > Thanks for the answer. > > Igor Tandetnik wrote: >> This monstrosity gives the correct answer in your specific example, but >> it relies on there being exactly two tags per folder. > It can be any number of tags per folder. > > Igor Tandetnik wrote: >> >> Consider normalizing your database. Split into two tables - folders and >> tags - with a one-to-many relationship between them. Then the solution >> would be trivial. > How can I do that considering that it can be any numbers of tags?
That is exactly why you would normalize them. Google for "normalizing a database" and then read up on it. It will help. Here is one way -- Given: ~/Music|classic,rock ~/Music|classic,rock ~/Pictures|art,photos ~/Pictures|art,photos ~/Pictures|art,photos CREATE TABLE folders (folder_id INTEGER PRIMARY KEY, folder_name TEXT); CREATE TABLE tags (tag_id INTEGER PRIMARY KEY, tag_name TEXT, folder_id INTEGER); Now, the above data can be INSERTed in the above two tables so they look like folders (columns separated by |) ---------- 1|~/Music 2|~/Pictures tags ------ 1|classic|1 2|rock|1 3|art|2 4|photos|2 Good luck. Please do read basic SQL tutorials on normalization. > > > Igor Tandetnik wrote: >> >> select >> (select count(*) from (select distinct folder from t1)), >> (select count(*) from ( >> select replace(rtrim(tags,'abcdefghijklmnopqrstuvwxyz'),',','') >> from t1 >> union >> select replace(ltrim(tags,'abcdefghijklmnopqrstuvwxyz'),',','') >> from t1)); >> >> Same caveat applies. > Ok, this answer my question at least for normal columns. Thanks. > > How should I do to store tags in my database and been able to count them? Do > you know how other programs like firefox do this? -- Puneet Kishor http://www.punkish.org/ Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/ Open Source Geospatial Foundation (OSGeo) http://www.osgeo.org/ _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
