I am trying to develop a "tagging" system, whereby each row in a table can be tagged with arbitrary number of tags.
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); foo ---- 1, one 2, two 3, three 4, four 5, five tag ---- 1, good 2, bad 3, ugly 4, bar foo_tag ---------- 1, 1 ('one' is tagged with 'good') 1, 2 ('one' is tagged with 'bad') 1, 4 ('one' is tagged with 'bar') 2, 2 ('two' is tagged with 'bad') 2, 4 ('two' is tagged with 'bar') 3, 1 ('three' is tagged with 'good') 3, 3 ('three' is tagged with 'ugly') 3, 4 ('three' is tagged with 'bar') and so on I want to find all rows in ‘foo’ that have a tag ‘bar’ and display all their tags that are NOT ‘bar’, that is, I want to display the following result f_name t_name ------ ------- one good one bad two bad three good three ugly So, one, two and three are tagged with the tag ‘bar’, but I am showing all the other tags. Ultimately, I want to display the above results as shown below, but that is an exercise to be solved in the application -- good - one - three bad - one - two ugly - three I have the following solution. Could I do better or differently? SELECT f.f_name, t.t_name FROM foo f JOIN foo_tag ft ON f.f_id = ft.f_id JOIN tag t ON ft.t_id = t.t_id WHERE ft.t_id != ‘bar’ AND ft.f_id IN ( SELECT f_id FROM foo_tag WHERE t_id = ‘bar’ ) ORDER BY t.t_id -- Puneet Kishor _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users