I have a schema in SQLite 3 that looks like the following:
CREATE TABLE tags(id INTEGER PRIMARY KEY, tag TEXT NOT NULL, UNIQUE(tag)); CREATE TABLE files(id INTEGER PRIMARY KEY, name TEXT NOT NULL, UNIQUE(name)); CREATE TABLE filetags(file_id INT NOT NULL, tag_id INT NOT NULL, UNIQUE(file_id, tag_id), FOREIGN KEY(file_id) REFERENCES files(id), FOREIGN KEY(tag_id) REFERENCES tags(id));
I've been trying to write a query that, for a given file id, shows every single tag and if that tag is set for that ID. The closest I can get is something like the following:
SELECT * FROM tags t LEFT OUTER JOIN filetags ft ON ft.tag_id=t.id LEFT OUTER JOIN files f ON f.id=ft.file_id WHERE f.id=@0 OR f.id IS NULL
That will work for a file that has 1 or more tag, but for a file with no tags, it excludes all the tags that have at least one file. I've tried several variations of this, but seem to be limited by having to work around sqlite's lack of full and right joins.