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

Answer 2

After multiple tests, I've come up with something like this:

SELECT a.id, a.rev, a.content, c.val, c.type, c.criteria
FROM `docs` a
  JOIN 
  (SELECT doc_id,criteria,
       LEFT(gctv,1) AS 'Type',
       SUBSTRING(SUBSTRING_INDEX(REPLACE(gctv,',',' '),' ',1),2) AS 'val' 
  FROM
    (SELECT doc_id,criteria,
            CASE 
            WHEN INSTR(ctv,@type) <> 0 THEN REPLACE(MID(ctv,INSTR(ctv,@type)) ,' ','')
            WHEN INSTR(ctv,@type)=0 THEN REPLACE(ctv,' ','') END AS gctv 
     FROM          
        (SELECT doc_id ,criteria,
                GROUP_CONCAT(tv ORDER BY val ASC) AS ctv 
          FROM
             (SELECT doc_id,criteria,val,
                     CONCAT_WS(' ',TYPE,VAL) tv
                FROM doc_val
                     WHERE criteria='L'
              )A
           GROUP BY doc_id
          )B, (SELECT @type:='D') temp
      )C) c
    ON a.id=c.doc_id
    WHERE rev=1;

I try to break it down:

This is the core of the query,

SELECT doc_id,criteria,val,
       CONCAT_WS(' ',TYPE,VAL) tv
            FROM doc_val
                WHERE criteria='L';

Basically, what its doing here is to combine Type and val into one column, with the condition of criteria='L'. The results look like this:

The first outer query,

SELECT doc_id, criteria,
       GROUP_CONCAT(tv ORDER BY val ASC) AS ctv 
  FROM
      (SELECT doc_id,criteria,val,
              CONCAT_WS(' ',TYPE,VAL) tv
       FROM doc_val
          WHERE criteria='L'
       )A
  GROUP BY doc_id

is performing a GROUP_CONCAT based on the core query result and its grouped by doc_id. Which produces a result like below:

You notice in the GROUP_CONCAT I've added a condition to ORDER BY val ASC. This will return the smallest value first from left to right order.

Then we go to the third query:

SELECT doc_id,criteria,
        CASE 
        WHEN INSTR(ctv,@type) <> 0 THEN REPLACE(MID(ctv,INSTR(ctv,@type)) ,' ','')
        WHEN INSTR(ctv,@type)=0 THEN REPLACE(ctv,' ','') END AS gctv 
 FROM          
    (SELECT doc_id ,
            GROUP_CONCAT(tv ORDER BY val ASC) AS ctv 
      FROM
         (SELECT doc_id,val,
                 CONCAT_WS(' ',TYPE,VAL) tv
            FROM doc_val
                 WHERE criteria='L'
          )A
       GROUP BY doc_id
      )B, (SELECT @type:='D') temp

This is where the type condition is used and instead of typing one by one (which what I've done earlier), I use a variable so if the type condition is no longer 'D', you only need to change it from the variable. You will see more operators is used here.

INSTR is to find whether in the 'ctv' column has the @type variable which was set to 'D' or not. This will return the starting position of 'D'. For example, in the second image, the first result is [H 80,D 100,D 101] so the operator INSTR will look-up the position of first 'D' occurrence which will return 6 (counting from left to right including spaces and comma). The second return 0 because it did not find any D inside the column. CASE WHEN will check if the value=0 then it will return the value in the column as is, if the value <> 0, it will return value based on the position extracted from INSTR(ctv,@type). That is why I've added another operator to get the column value from the position (MID ). I've included REPLACE to remove the spaces in between type and val. To understand the query more, I have prepared a query breakdown of the operation below:

SELECT doc_id,criteria,
       INSTR(ctv,@type),
       MID(ctv,INSTR(ctv,@type)),
       REPLACE(MID(ctv,INSTR(ctv,@type)) ,' ',''),
       CASE 
           WHEN INSTR(ctv,@type) <> 0 
           THEN REPLACE(MID(ctv,INSTR(ctv,@type)) ,' ','')
           WHEN INSTR(ctv,@type)=0 
           THEN REPLACE(ctv,' ','') 
       END AS gctv,
       SUBSTRING(REPLACE(ctv,' ',''),1)
 FROM          
    (SELECT doc_id,criteria,
            GROUP_CONCAT(tv ORDER BY val ASC) AS ctv 
      FROM
         (SELECT doc_id,criteria,val,
                 CONCAT_WS(' ',TYPE,VAL) tv
            FROM doc_val
                 WHERE criteria='L'
          )A
       GROUP BY doc_id
      )B, (SELECT @type:='D') temp

Query above will return the following:

The last part here:

SELECT doc_id,
       LEFT(gctv,1) AS 'Type',
       SUBSTRING(SUBSTRING_INDEX(REPLACE(gctv,',',' '),' ',1),2) AS 'val' 

the first operator of REPLACE is to change commas into spaces (refer gctv result in photo above). Then with SUBSTRING_INDEX, it takes the first 'type+val' in the column then SUBSTRING will return value from position 2 - which was from taken from val column (this is assuming that your type column only consists of single character).

Fiddle here: DB Fiddle

Answer 3

This should give you your desired outcome

See demo here http://sqlfiddle.com/#!9/0e32ec/32

Select *
from(
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'      
Order by `a`.`id`, FIELD(b.type, 'D','H','A'), b.`val` ) as sub
GROUP BY `id`

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