0

You have two tables: 1. docs 2. doc_val

Point of focus is table : doc_val , it has doc_id FK from table docs , field critera which will be our condition.

Mysql schema:

CREATE TABLE IF NOT EXISTS `docs` (
`id` int(6) unsigned NOT NULL,
`rev` int(3) unsigned NOT NULL,
`content` varchar(200) NOT NULL,
PRIMARY KEY (`id`)
) DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `doc_val` (
`id` int(6) unsigned NOT NULL,
`doc_id` int(6) unsigned NOT NULL,
`val` int(3) unsigned NOT NULL,
`type` varchar(2) NOT NULL,
`criteria` varchar(2) NOT NULL,
PRIMARY KEY (`id`)
) DEFAULT CHARSET=utf8;

INSERT INTO `docs` (`id`, `rev`, `content`) VALUES
('1', '1', 'The earth is flat'),
('2', '1', 'One hundred angels can dance on the head of a pin'),
('3', '1', 'The earth is flat and rests on a bull\'s horn'),
('4', '4', 'The earth is like a ball.');

INSERT INTO `doc_val` (`id`, `doc_id`, `val`, `type`, `criteria`) VALUES
('1', '1', 100, 'D', 'L'),
('2', '1', 101, 'D', 'L'),
('3', '1', 80, 'H', 'L'),
('4', '2', 10, 'H', 'S'),
('5', '2', 90, 'H', 'L'),
('6', '3', 100, 'D', 'L'),
('7', '3', 100, 'D', 'L');

expected output:

enter image description here

Utsav Shrestha
  • 107
  • 1
  • 1
  • 6

1 Answers1

0
DECLARE curIds CURSOR FOR SELECT DISTINCT doc_id FROM doc_val;
DECLARE id INT;
CREATE TEMPORARY TABLE temp(id int, doc_id int, val int, type char(1), criteria char(1));

OPEN curIds;

read_loop: LOOP
  FETCH curIds INTO id;
  INSERT INTO temp
   (Select * from doc_val 
    where val = (select min(val) 
                 from doc_val 
                 where doc_id = id)) AND criteria = 'L'

END LOOP;

SELECT * FROM temp;

Probably there is a syntax error but i hope you caught the idea.

Jon
  • 166
  • 6