On Fri, Jul 24, 2009 at 09:20:29PM -0500, P Kishor scratched on the wall: > I am trying to develop a "tagging" system, whereby each row in a table > can be tagged with arbitrary number of tags.
This smells of a Relational division problem. If you're dealing with tags you might want to have a look at that (Celko has a few good articles on it). Since SQL lacks a native Relational division operator, chances are a solution in that direction is going to be more complex -- at least for this problem. But any time I've done tags or attributes, sooner or later I find myself needing to do a division. They come in handy any time you say "my data is vertical but I need it horizontal." You might want to read up on them just to have that knowledge available. > TABLE foo (f_id INTEGER PRIMARY KEY, f_name TEXT); > TABLE tag (t_id INTEGER PRIMARY KEY, t_name TEXT); > TABLE foo_tag (f_id INTEGER, t_id INTEGER); > I have the following solution. Could I do better or differently? I'm not sure about "better", but here's different: sqlite> SELECT foo.f_name, tag.t_name ...> FROM tag AS target ...> NATURAL JOIN foo_tag AS target_ft ...> NATURAL JOIN foo ...> NATURAL JOIN foo_tag ...> NATURAL JOIN tag ...> WHERE target.t_name = 'bar' ...> AND tag.t_id != target.t_id ...> ORDER BY foo.f_name, tag.t_name; This basically folds your IN sub-select back into the main query. We join "foo" to the tag table in two directions... one to find the search target tag id and the other to produce the output. "tag AS target" with the first WHERE clause should return one row. We join that through "foo_tag AS target_ft" to get a list of foo ids that have the search target tag. We then build the normal output list by joining that back through the foo_tag bridge table to the tags, and throw out any rows with an output tag id that matches the search target tag id. Simple! Best of all, the target only appears in the query once, and your name convention means we can use NATURAL JOINs to keep things clean. It also totally falls apart if you need to search on more than one tag. That's where Relational division comes in. -j (who had to draw a picture to get it right) -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Our opponent is an alien starship packed with atomic bombs. We have a protractor." "I'll go home and see if I can scrounge up a ruler and a piece of string." --from Anathem by Neal Stephenson _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
