inner join with subquery results differs for these data sets

CREATE TABLE IF NOT EXISTS `wcd` (
  `id` int(6) unsigned NOT NULL,
  `wid` int(11) unsigned NOT NULL,
  PRIMARY KEY (`id`)
) DEFAULT CHARSET=utf8;
INSERT INTO `wcd` (`id`, `wid`) VALUES
  ('168', '5685'),
  ('167', '5685'),
  ('166', '5685'),
  ('165', '5685'),
  ('164', '5685'),
  ('163', '5685'),
  ('162', '5684'),
  ('161', '5684');

  CREATE TABLE IF NOT EXISTS `cases` (
  `id` int(6) unsigned NOT NULL,
  `wcd_id` int(11) unsigned NOT NULL,
  `reason_id` int(11) unsigned NOT NULL,
  PRIMARY KEY (`id`)
) DEFAULT CHARSET=utf8;
INSERT INTO `cases` (`id`, `wcd_id`, `reason_id`) VALUES
  ('20', '168', '4'),
  ('19', '168', '1'),
  ('18', '167', '6'),
  ('17', '167', '5'),
  ('16', '166', '4'),
  ('15', '166', '1'),
  ('14', '165', '4'),
  ('13', '165', '1'),
  ('12', '164', '1'),
  ('11', '163', '4'),
  ('10', '162', '1'),
  ('9', '162', '4'),
  ('8', '162', '5'),
  ('7', '161', '5'),
  ('6', '161', '6');

the above two table has foreignkey relation with wcd.id = cases.wcd_id, Lets consider the records related to wcd.wid 5865. The result should be grouped by reason_id with the condition max(cases.id)

I used the query below to achieve this and got the result as expected.

SELECT d.id, d.wid, c.* FROM wcd d
LEFT JOIN cases c ON c.wcd_id = d.id
inner JOIN (SELECT MAX(id) AS max_id FROM cases GROUP BY reason_id) c2
ON c2.max_id = c.id
WHERE d.wid = 5685;

Result:

id  wid     id  wcd_id  reason_id
168 5685    19  168     1
168 5685    20  168     4
167 5685    17  167     5
167 5685    18  167     6

with the same query for 5684, the query returns 0 rows though there is data available for it. but I’m expecting the rows below.

id  wid     id  wcd_id  reason_id
162 5684    10  162     1
162 5684    9   162     4
162 5684    8   162     5
161 5684    6   161     6

What the issue with the query and what needs to be changed to get the result above for 5684.?

here is the sqlfiddle link

Answer

You need to look back at the wcd table to propery correlate, since you need the id of the row that has the “latest” reason per wid – and that column is not available in cases.

In MySQL 8.0, we would just use row_number()… but you tagged your question MySQL 5.6. I find that the simplest way to express this is with a correlated subquery:

SELECT d.id, d.wid, c.* 
FROM wcd d
INNER JOIN cases c ON c.wcd_id = d.id
WHERE c.id = (
    SELECT max(c2.id)
    FROM wcd d2
    INNER JOIN cases c2 ON c2.wcd_id = d2.id
    WHERE d2.wid = d.wid AND c2.reason_id = c.reason_id
)
AND d.wid = 5685;