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
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
