Trouble with full joins in SQLite3

Question

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.

maybe useful? http://stackoverflow.com/questions/1923259/full-outer-join-with-sqlite — Ryan Vincent, May 08 '16 at 07:54

score 1 · Accepted Answer · answered May 08 '16 at 23:12

Starting with the notion: "I want all files and all tags":

SELECT * FROM files f CROSS JOIN tags t

Now you need to append "whether or not that file actually has that tag set":

SELECT * FROM files f CROSS JOIN tags t
   LEFT JOIN filetags ft ON ft.file_id=f.id AND ft.tag_id=t.id

And for a bit of polish, a cosmetic alias for it:

SELECT f.*, t.*, (ft.file_id IS NOT NULL) AS file_has_tag
   FROM files f CROSS JOIN tags t
   LEFT JOIN filetags ft ON ft.file_id=f.id AND ft.tag_id=t.id

Trouble with full joins in SQLite3

1 Answers1