clear markdown feedback
clear markdown feedback
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 3364508 fiddles created (40614 in the last week).

CREATE TABLE IF NOT EXISTS task ( task_id SMALLINT NOT NULL AUTO_INCREMENT PRIMARY KEY, task_name VARCHAR(30) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 hidden batch(es)


INSERT INTO task (task_name) VALUES ('Eat'), -- 1 ('Sleep'), -- 2 ('Code'), -- 3 ('Shower'); -- 4
 hidden batch(es)


CREATE TABLE IF NOT EXISTS mood ( mood_id SMALLINT NOT NULL AUTO_INCREMENT PRIMARY KEY, mood_name VARCHAR(30) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 hidden batch(es)


INSERT INTO mood (mood_name) VALUES ('Calm'), -- 1 ('Excited'), -- 2 ('Happy'), -- 3 ('Mellow'), -- 4 ('Angry'), -- 5 ('Guilty'), -- 6 ('Sad'), -- 7 ('Stressed'), -- 8 ('Weird'), -- 9 ('Energetic'); -- 10
 hidden batch(es)


CREATE TABLE IF NOT EXISTS place ( place_id SMALLINT NOT NULL AUTO_INCREMENT PRIMARY KEY, place_name VARCHAR(30) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 hidden batch(es)


INSERT INTO place (place_name) VALUES ('Bedroom'), -- 1 ('Bathroom'), -- 2 ('Dining room'), -- 3 ('Emergency room'), -- 4 ('Game room'), -- 5 ('Garage'), -- 6 ('Kitchen'), -- 7 ('Living room'), -- 8 ('Studio'), -- 9 ('Solarium'); -- 10
 hidden batch(es)


CREATE TABLE IF NOT EXISTS link_table ( task_id SMALLINT NOT NULL, mood_id SMALLINT NOT NULL, place_id SMALLINT NOT NULL, FOREIGN KEY (task_id) REFERENCES task (task_id), FOREIGN KEY (mood_id) REFERENCES mood (mood_id), FOREIGN KEY (place_id) REFERENCES place (place_id), PRIMARY KEY (task_id, mood_id, place_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 hidden batch(es)


INSERT INTO link_table (task_id, mood_id, place_id) VALUES ('4','9','5'), ('4','8','7'), ('1','3','8'), ('1','5','9'), ('4','3','5'), ('4','8','5'), ('1','9','5'), ('2','1','5'), ('2','3','6'), ('4','4','10'), ('2','9','3'), ('4','2','9'), ('4','4','5'), ('3','4','4'), ('2','3','8'), ('4','3','2'), ('4','5','4'), ('4','8','3'), ('2','2','4'), ('1','1','4'), ('3','5','10'), ('4','8','10'), ('1','7','5'), ('2','3','9'), ('3','3','8'), ('4','9','3'), ('4','8','9'), ('4','3','6'), ('2','8','2');
 hidden batch(es)


WITH cte1 AS (SELECT * FROM task ORDER BY RAND() LIMIT 1), cte2 AS (SELECT mood.* FROM link_table JOIN mood USING (mood_id) JOIN cte1 USING (task_id) ORDER BY RAND() LIMIT 1) SELECT cte1.*, cte2.*, place.* FROM link_table JOIN cte1 USING (task_id) JOIN cte2 USING (mood_id) JOIN place USING (place_id) ORDER BY RAND() LIMIT 1
task_id task_name mood_id mood_name place_id place_name
 hidden batch(es)


WITH cte AS (SELECT * FROM link_table ORDER BY RAND() LIMIT 1) SELECT cte.*, task.*, mood.*, place.* FROM cte JOIN task USING (task_id) JOIN mood USING (mood_id) JOIN place USING (place_id)
task_id mood_id place_id task_id task_name mood_id mood_name place_id place_name
2 2 4 2 Sleep 2 Excited 4 Emergency room
 hidden batch(es)