MySql first result before and after result set

Question

I've got this awful query where I use IFNULL to determine whether there is a result before and a result after my result set. The result set is determined by a period in time, see may 5th till may 10th. This period of time is visualised in my application as a line graph. I want the first result before and after the period of may 5th till may 10th, if there is any (therefore the use of IFNULL) because I want the line of grahp to be continous. If there lays a result before the visualised period in time, I need to get started with drawing the line from that point, intersecting the graphs area, though it is not on the screen.

See the image for a visual example. The first graph does not include a measurement before and after the visualised period (the box represents the area of the graph actually displayed to the user), and the second graph does intersect the Y axis as there is one measurement before the displayed period, and one after.

enter image description here

Now so far so good, my awful query works. That is, if I want to just obtain the measurements of one entity. But now I want to obtain this result for a set of entities in one query, with regard to performance. Better query once to obtain measurements from 200 entities, than query per entity = 200 times. So I thought I change the query from WHERE Entity.Id=1 to WHERE Entity.Id IN (1, 2, 3, 4). The problem now is, that the period to include measurements before and after the graph is generalized, and not evaluated per entity and its measurements.

Below is the query that works per entity (a cow in my specific case)

SELECT 
    *
FROM
    `measurements`
WHERE
    `CowId` = 23 AND `CellCount` >= 0
        AND (`Date` BETWEEN IFNULL((SELECT 
                    MAX(`Date`)
                FROM
                    `measurements`
                WHERE
                    `CowId` = 23
                        AND `Date` < '2014-03-28 00:00:00'),
            '2014-03-28 00:00:00') AND IFNULL((SELECT 
                    MIN(`Date`)
                FROM
                    `measurements`
                WHERE
                    `CowId` = 23
                        AND `Date` > '2014-04-11 23:59:59'),
            '2014-04-11 23:59:59'))
ORDER BY `CowId` ASC , `Date` ASC

And here is my attempt to query all cows and their measurements in one query

SELECT 
    *
FROM
    `measurements`
WHERE
    `CowId` IN (23 , 22, 19, 18, 17, 16, 15, 20, 21, 14)
        AND `CellCount` >= 0
        AND (`Date` BETWEEN IFNULL((SELECT 
                    MAX(`Date`)
                FROM
                    `measurements`
                WHERE
                    `CowId` IN (23 , 22, 19, 18, 17, 16, 15, 20, 21, 14)
                        AND `Date` < '2014-03-28 00:00:00'),
            '2014-03-28 00:00:00') AND IFNULL((SELECT 
                    MIN(`Date`)
                FROM
                    `measurements`
                WHERE
                    `CowId` IN (23 , 22, 19, 18, 17, 16, 15, 20, 21, 14)
                        AND `Date` > '2014-04-11 23:59:59'),
            '2014-04-11 23:59:59'))
ORDER BY `CowId` ASC , `Date` ASC

Oh deer, what an awful looking question I posted here on SO. Hopefully I made myself clear. Thanks!

score 1 · Accepted Answer · answered Apr 11 '14 at 06:58

In subqueries there, should be connection to CowID in main query... Your query should looks like:

SELECT 
    *
FROM
  `measurements` `m1`
  WHERE
      `m1`.`CowId` IN (23 , 22, 19, 18, 17, 16, 15, 20, 21, 14)
    AND `m1`.`CellCount` >= 0
    AND (`m1`.`Date` BETWEEN IFNULL((SELECT 
                MAX( `m2`.`Date`)
            FROM
                `measurements` `m2`
            WHERE
               `m2`.`CowId` =  `m1`.`CowId`
                    AND `m2`.`Date` < '2014-03-28 00:00:00'),
        '2014-03-28 00:00:00') AND IFNULL((SELECT 
                MIN( `m3`.`Date`)
            FROM
                `measurements` `m3`
            WHERE
                `m3`.`CowId` =  `m1`.`CowId`
                    AND  `m3`.`Date` > '2014-04-11 23:59:59'),
        '2014-04-11 23:59:59'))
  ORDER BY  `m1`.`CowId` ASC ,`m1`.`Date` ASC

Wowwie! Copy paste and it worked. I didn't now I could relate queries like this. Thanks so much for the insight and especially for the effort to make this query work. You saved me a headache and my day! — Mike de Klerk, Apr 11 '14 at 07:43

MySql first result before and after result set

1 Answers1