MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select version();


version()


8.0.23


CREATE TABLE IF NOT EXISTS `m_medication` (
  `m_id` varchar(10) NOT NULL,
  `m_name` varchar(500) DEFAULT NULL,
  PRIMARY KEY (`m_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `m_medication` (`m_id`, `m_name`) VALUES('M00001', 'drug1');
INSERT INTO `m_medication` (`m_id`, `m_name`) VALUES('M00002', 'drug2');
INSERT INTO `m_medication` (`m_id`, `m_name`) VALUES('M00003', 'drug3');
CREATE TABLE IF NOT EXISTS `i_ingredients` (
  `i_id` varchar(10) NOT NULL,
  `i_name` varchar(500) DEFAULT NULL,
  PRIMARY KEY (`i_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `i_ingredients` (`i_id`, `i_name`) VALUES('I001', 'ingredient1');
INSERT INTO `i_ingredients` (`i_id`, `i_name`) VALUES('I002', 'ingredient2');
INSERT INTO `i_ingredients` (`i_id`, `i_name`) VALUES('I003', 'ingredient3');
INSERT INTO `i_ingredients` (`i_id`, `i_name`) VALUES('I004', 'ingredient4');
INSERT INTO `i_ingredients` (`i_id`, `i_name`) VALUES('I005', 'ingredient5');
INSERT INTO `i_ingredients` (`i_id`, `i_name`) VALUES('I006', 'ingredient6');
CREATE TABLE IF NOT EXISTS `mi_med-ing` (
  `mi_id` varchar(10) NOT NULL,
  `mi_i_id` varchar(10) DEFAULT NULL,
  `mi_m_id` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`mi_id`),
  KEY `mi_i_id` (`mi_i_id`),
  KEY `mi_m_id` (`mi_m_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `mi_med-ing` (mi_id, mi_i_id, mi_m_id) VALUES('MI001', 'I001', 'M00001'); 
INSERT INTO `mi_med-ing` (mi_id, mi_i_id, mi_m_id) VALUES('MI002', 'I002', 'M00001'); 
INSERT INTO `mi_med-ing` (mi_id, mi_i_id, mi_m_id) VALUES('MI003', 'I001', 'M00002'); 
INSERT INTO `mi_med-ing` (mi_id, mi_i_id, mi_m_id) VALUES('MI004', 'I005', 'M00002'); 
INSERT INTO `mi_med-ing` (mi_id, mi_i_id, mi_m_id) VALUES('MI005', 'I002', 'M00003'); 
INSERT INTO `mi_med-ing` (mi_id, mi_i_id, mi_m_id) VALUES('MI006', 'I005', 'M00003');
ALTER TABLE `mi_med-ing`
  ADD CONSTRAINT `mi_med-ing_ibfk_1` FOREIGN KEY (`mi_i_id`) REFERENCES `i_ingredients` (`i_id`),
  ADD CONSTRAINT `mi_med-ing_ibfk_2` FOREIGN KEY (`mi_m_id`) REFERENCES `m_medication` (`m_id`);
CREATE PROCEDURE getMeds (IN all1 VARCHAR(10), IN all2 VARCHAR(10), IN all3 VARCHAR(10), 
                          IN unall1 VARCHAR(10), IN unall2 VARCHAR(10), IN unall3 VARCHAR(10))
BEGIN
  SELECT m.*, i.*
  FROM m_medication m
  JOIN `mi_med-ing` mi ON mi.mi_m_id = m.m_id
  JOIN i_ingredients i ON i.i_id = mi.mi_i_id
  WHERE m.m_id IN
  (
    SELECT mi_m_id
    FROM `mi_med-ing`
    GROUP BY mi_m_id
    HAVING (all1   = '' OR SUM(mi_i_id = all1)   > 0)
       AND (all2   = '' OR SUM(mi_i_id = all2)   > 0)
       AND (all3   = '' OR SUM(mi_i_id = all3)   > 0)
       AND (unall1 = '' OR SUM(mi_i_id = unall1) = 0)
       AND (unall2 = '' OR SUM(mi_i_id = unall2) = 0)
       AND (unall3 = '' OR SUM(mi_i_id = unall3) = 0)
  )
  ORDER BY m.m_id, i.i_id;
END
call getMeds('I005', '', '', 'I001', '', '');


m_id
m_name
i_id
i_name


M00003
drug3
I002
ingredient2

M00003
drug3
I005
ingredient5