By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE users (
id int NOT NULL AUTO_INCREMENT,
name varchar(255),
PRIMARY KEY (id)
);
INSERT INTO users (id, name) VALUES
(1, 'John');
CREATE TABLE workouts (
id int NOT NULL AUTO_INCREMENT,
name varchar(255),
userId int,
PRIMARY KEY (id),
CONSTRAINT workouts_ibfk_1 FOREIGN KEY (userId) REFERENCES users (id) ON DELETE CASCADE ON UPDATE CASCADE
);
INSERT INTO workouts (id, name, userId) VALUES
(1, 'Mon', 1),
(2, 'Tue', 1),
(3, 'Wed', 1);
Records: 3 Duplicates: 0 Warnings: 0
CREATE TABLE tags (
id int NOT NULL AUTO_INCREMENT,
name varchar(255),
PRIMARY KEY (id)
);
INSERT INTO tags (id, name) VALUES
(1, 'Legs'),
(2, 'Chest'),
(3, 'Shoulders'),
(4, 'Abs'),
(5, 'Back'),
(6, 'Arms');
Records: 6 Duplicates: 0 Warnings: 0
CREATE TABLE workout_tag (
workoutId int NOT NULL,
tagId int NOT NULL,
PRIMARY KEY (workoutId, tagId),
CONSTRAINT workout_tag_ibfk_1 FOREIGN KEY (workoutId) REFERENCES workouts (id) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT workout_tag_ibfk_2 FOREIGN KEY (tagId) REFERENCES tags (id) ON DELETE CASCADE ON UPDATE CASCADE
);
INSERT INTO workout_tag VALUES
(1, 1),
(1, 3),
(1, 4),
(2, 2),
(2, 5),
(3, 6),
(3, 4);
Records: 7 Duplicates: 0 Warnings: 0
SELECT users.name, workouts.name, tags.name
FROM users
JOIN workouts ON users.id = workouts.userId
JOIN workout_tag ON workouts.id = workout_tag.workoutId
JOIN tags ON workout_tag.tagId = tags.id
ORDER BY users.id, workouts.id, tags.id;
| name | name | name |
|---|---|---|
| John | Mon | Legs |
| John | Mon | Shoulders |
| John | Mon | Abs |
| John | Tue | Chest |
| John | Tue | Back |
| John | Wed | Abs |
| John | Wed | Arms |
-- Legs, Shoulders, Abs
SELECT *
FROM workouts
WHERE NOT EXISTS (
SELECT 1
FROM (VALUES ROW(1), ROW(3), ROW(4)) AS required_tags(id)
WHERE NOT EXISTS (
SELECT 1
FROM workout_tag
WHERE workout_tag.workoutId = workouts.id AND workout_tag.tagId = required_tags.id
)
);
| id | name | userId |
|---|---|---|
| 1 | Mon | 1 |
-- Legs, Shoulders, Abs
SELECT *
FROM workouts
WHERE id IN (
SELECT workoutId
FROM workout_tag
WHERE tagId IN (1, 3, 4)
GROUP BY workoutId
HAVING COUNT(*) = 3
)
| id | name | userId |
|---|---|---|
| 1 | Mon | 1 |
-- Abs, Arms
SELECT *
FROM workouts
WHERE NOT EXISTS (
SELECT 1
FROM (VALUES ROW(4), ROW(6)) AS required_tags(id)
WHERE NOT EXISTS (
SELECT 1
FROM workout_tag
WHERE workout_tag.workoutId = workouts.id AND workout_tag.tagId = required_tags.id
)
);
| id | name | userId |
|---|---|---|
| 3 | Wed | 1 |
-- Abs, Arms
SELECT *
FROM workouts
WHERE id IN (
SELECT workoutId
FROM workout_tag
WHERE tagId IN (4, 6)
GROUP BY workoutId
HAVING COUNT(*) = 2
)
| id | name | userId |
|---|---|---|
| 3 | Wed | 1 |
-- Legs, Chest, Shoulders
SELECT *
FROM workouts
WHERE NOT EXISTS (
SELECT 1
FROM (VALUES ROW(1), ROW(2), ROW(3)) AS required_tags(id)
WHERE NOT EXISTS (
SELECT 1
FROM workout_tag
WHERE workout_tag.workoutId = workouts.id AND workout_tag.tagId = required_tags.id
)
);
| id | name | userId |
|---|
-- Legs, Chest, Shoulders
SELECT *
FROM workouts
WHERE id IN (
SELECT workoutId
FROM workout_tag
WHERE tagId IN (1, 2, 3)
GROUP BY workoutId
HAVING COUNT(*) = 3
)
| id | name | userId |
|---|