Mysql: rows having minimum value and prioritize where condition based on case

Question

I have two tables, first table is: docs and another table: doc_val

with doc_id as foreign key from table docs

I need to get list of docs (including val, type and criteria from doc_val ) which matches certain conditions, say for example: doc_val.criteria = 'L' and docs.rev = 1

While getting this list of docs I also need to make sure that the doc_val.val for given doc_id is the minimum. AND also make sure that doc_val.type = 'D', given that the there exists doc_val.type = 'D' ELSE we should just simply get doc_val for given doc_id which has minimum doc_val.val.

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');

With this query if I take b.type = 'D' simply as part of where condition, I loose all docs which do not have type as D.

SELECT a.id, a.rev, a.content, b.val, b.type, b.criteria
FROM `docs` a
  JOIN `doc_val` b ON b.doc_id = a.id
  WHERE a.`rev` = 1 and b.type = 'D' and b.criteria = 'L'      
GROUP BY `a`.`id`
HAVING min(b.`val`)

If we do not consider type=D as condition at all, the output for this condition kind of worked but,

SELECT a.id, a.rev, a.content, b.val, b.type, b.criteria
FROM `docs` a
  JOIN `doc_val` b ON b.doc_id = a.id
  WHERE a.`rev` = 1 and b.criteria = 'L'      
GROUP BY `a`.`id`
HAVING min(b.`val`)

final expected output:

But Technically without type=D as condition, I should have received an output for doc.id = 1 as:

1. So I am probably doing something wrong with use of HAVING any direction would be helpful.

2. Is it possible to prioritize doc_val.type with doc_val.type = D, such that when a row with type = D it takes priority, if it doesn't exist simply take one with minimum value without considering type?

Answer 1

You can try below -

DEMO

SELECT 
    *
FROM
    (SELECT 
        a.id, a.rev, a.content, MIN(b.val) val, b.type, b.criteria
    FROM
        `docs` a
    JOIN `doc_val` b ON b.doc_id = a.id
    WHERE
        a.`rev` = 1 AND b.criteria = 'L'
    GROUP BY a.id , a.rev , a.content , b.type , b.criteria) A
WHERE
    val IN (SELECT 
            MAX(val)
        FROM
            (SELECT 
                a.id, a.rev, a.content, MIN(b.val) val, b.type, b.criteria
            FROM
                `docs` a
            JOIN `doc_val` b ON b.doc_id = a.id
            WHERE
                a.`rev` = 1 AND b.criteria = 'L'
            GROUP BY a.id , a.rev , a.content , b.type , b.criteria) B
        WHERE
            A.content = B.content)

OUTPUT:

id  rev content                                            val   type  criteria                  
1   1   The earth is flat                                   100  D     L
2   1   One hundred angels can dance on the head of a pin   90   H     L
3   1   The earth is flat and rests on a bull's horn        100  D     L