1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
|
CREATE TABLE tags (
id integer NOT NULL PRIMARY KEY,
name varchar(255),
description text
);
CREATE TABLE files (
id integer NOT NULL PRIMARY KEY,
canonical_name varchar(255),
full_name varchar(255),
description text,
date varchar(255)
);
CREATE TABLE file_tags (
file integer,
tag integer,
FOREIGN KEY(file) REFERENCES files(id),
FOREIGN KEY(tag) REFERENCES tags(id)
);
INSERT INTO tags VALUES(0, 'inria', 'Document lié à Inria');
INSERT INTO tags VALUES(1, 'enseirb', 'Document lié à Enseirb');
INSERT INTO files VALUES(0, 'contrat_these.pdf', 'Contrat de travail pour la thèse.');
INSERT INTO file_tags VALUES(0 ,0);
-- Query: all files related to Inria
SELECT name, description FROM (
files JOIN file_tags ON files.id = file_tags.file
) WHERE file_tags.tag = 0;
|
