Many to Many Tags

-- Drill 13 — جدول many-to-many بمفتاح مركب — SQLite 3.x+
PRAGMA foreign_keys = ON;

CREATE TABLE lessons (
  id INTEGER PRIMARY KEY,
  title TEXT NOT NULL
);

CREATE TABLE tags (
  id INTEGER PRIMARY KEY,
  label TEXT NOT NULL
);

CREATE TABLE lesson_tags (
  lesson_id INTEGER NOT NULL REFERENCES lessons(id) ON DELETE CASCADE,
  tag_id INTEGER NOT NULL REFERENCES tags(id) ON DELETE CASCADE,
  PRIMARY KEY (lesson_id, tag_id)
);

INSERT INTO lessons VALUES (1, 'Joins');
INSERT INTO tags VALUES (10, 'sql'), (11, 'relationships');
INSERT INTO lesson_tags VALUES (1, 10), (1, 11);

SELECT lessons.title, tags.label
FROM lesson_tags
INNER JOIN lessons ON lessons.id = lesson_tags.lesson_id
INNER JOIN tags ON tags.id = lesson_tags.tag_id
ORDER BY tags.label;