clear markdown compare help best fiddles feedback dbanow.uk a leap of faith?
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 2335853 fiddles created (27453 in the last week).

CREATE TABLE `gift_certificate` ( `gift_certificate_id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(50) DEFAULT NULL, `description` varchar(100) DEFAULT NULL, `price` decimal(8,2) DEFAULT NULL, `create_date` timestamp NULL DEFAULT CURRENT_TIMESTAMP, `last_update_date` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `duration` int(5) DEFAULT NULL, PRIMARY KEY (`gift_certificate_id`) ); INSERT INTO `gift_certificate` VALUES (9,'pizza_certificate','50% discount for 5 pizzas',10.50,'2020-01-18 15:53:21','2020-01-18 15:53:21',4), (11,'ninja_sushi_certificate','every second sushi is free',3.00,'2020-01-18 15:58:24','2020-01-18 15:58:24',2), (12,'salad','every salad 25% discount',2.00,'2020-01-18 15:59:14','2020-01-19 18:49:53',1), (13,'kfc_delivery_ceritificate','free delivery over 10$ ',2.50,'2020-01-18 16:00:52','2020-01-18 16:00:52',10), (47,'certificate_for_rent_car','car rent xml description',3.55,'2020-01-20 21:36:26','2020-01-25 12:59:08',3); SELECT * FROM gift_certificate;
gift_certificate_id name description price create_date last_update_date duration
9 pizza_certificate 50% discount for 5 pizzas 10.50 2020-01-18 15:53:21 2020-01-18 15:53:21 4
11 ninja_sushi_certificate every second sushi is free 3.00 2020-01-18 15:58:24 2020-01-18 15:58:24 2
12 salad every salad 25% discount 2.00 2020-01-18 15:59:14 2020-01-19 18:49:53 1
13 kfc_delivery_ceritificate free delivery over 10$ 2.50 2020-01-18 16:00:52 2020-01-18 16:00:52 10
47 certificate_for_rent_car car rent xml description 3.55 2020-01-20 21:36:26 2020-01-25 12:59:08 3
 hidden batch(es)


CREATE TABLE `tag` ( `tag_id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(50) DEFAULT NULL, PRIMARY KEY (`tag_id`) ); INSERT INTO `tag` VALUES (6,'pizza'), (8,'certificate'), (9,'car'), (12,'sushi'), (13,'food'), (14,'delivery'), (15,'salad'), (16,'kfc'), (21,'tasty'), (53,'rent'); SELECT * FROM tag;
tag_id name
6 pizza
8 certificate
9 car
12 sushi
13 food
14 delivery
15 salad
16 kfc
21 tasty
53 rent
 hidden batch(es)


CREATE TABLE `tag_gift_certificate` ( `tag_id` int(11) NOT NULL, `gift_certificate_id` int(11) NOT NULL, PRIMARY KEY (`tag_id`,`gift_certificate_id`), KEY `fk_tag_gift_certificate_gift_certificate_gift_certificate_i_idx` (`gift_certificate_id`), KEY `fk_tag_gift_certificate_gift_tag_tag_id_idx` (`tag_id`), CONSTRAINT `fk_tag_gift_certificate_gift_certificate_gift_certificate_id` FOREIGN KEY (`gift_certificate_id`) REFERENCES `gift_certificate` (`gift_certificate_id`) ON DELETE CASCADE, CONSTRAINT `fk_tag_gift_certificate_gift_tag_tag_id` FOREIGN KEY (`tag_id`) REFERENCES `tag` (`tag_id`) ON DELETE CASCADE ); INSERT INTO `tag_gift_certificate` VALUES (6,9), (8,9), (12,11), (13,11), (14,11), (13,12), (14,12), (15,12), (21,12), (13,13), (14,13), (16,13), (9,47), (53,47); SELECT * FROM tag_gift_certificate;
tag_id gift_certificate_id
6 9
8 9
12 11
13 11
14 11
13 12
14 12
15 12
21 12
13 13
14 13
16 13
9 47
53 47
 hidden batch(es)


SELECT g.gift_certificate_id, g.name, g.description, g.price, g.create_date, g.last_update_date, g.duration, group_concat(t.name order by t.name) as 'tags' FROM tag_gift_certificate JOIN gift_certificate g USING (gift_certificate_id) JOIN tag t USING (tag_id) GROUP BY g.gift_certificate_id, g.name, g.description, g.price, g.create_date, g.last_update_date, g.duration HAVING INSTR(tags, 'food') AND INSTR(tags, 'kfc');
gift_certificate_id name description price create_date last_update_date duration tags
13 kfc_delivery_ceritificate free delivery over 10$ 2.50 2020-01-18 16:00:52 2020-01-18 16:00:52 10 delivery,food,kfc
 hidden batch(es)