inner join with subquery results differs for these data sets

Question

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

When you get a result that you don't expect, isolate the first subexpression that doesn't output what you expect (which includes confirming that others all give what you expect) & its input & output & say what you expected & why. (Debugging fundamental.) PS You don't clearly say how desired output is a function of input. Use enough words, sentences & references to parts of examples to clearly & fully say what you mean. When giving a business relation(ship)/association or table (base or query result), say what a row in it states about the business situation in terms of its column values. — philipxy, Jun 05 '20 at 01:31
Does this answer your question? [Problems with INNER JOIN and LEFT/RIGHT OUTER JOIN](https://stackoverflow.com/q/16442900/3404097) — philipxy, Jun 05 '20 at 01:36
LEFT JOIN ON returns INNER JOIN ON rows UNION ALL unmatched left table rows extended by NULLs. Always know what INNER JOIN ON you want as part of an OUTER JOIN ON. After a LEFT JOIN ON, a WHERE or INNER JOIN that requires a right [sic] table column to be not NULL removes any rows with introduced NULLs, ie leaves only INNER JOIN ON rows, ie "turns OUTER JOIN into INNER JOIN". You have that. It might not be a problem here, because you might need the INNER JOIN you are effectively expressing — philipxy, Jun 05 '20 at 01:36

score 1 · Accepted Answer · answered Jun 04 '20 at 22:33

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;

score 1 · Answer 2 · answered Jun 04 '20 at 22:33

1

Then you must use MIN and get rid of the Where Clause.because

 ('162', '5684')
  ('161', '5684')

because

SELECT 
d.id
, d.wid
, 
c.*
FROM 
wcd d
LEFT JOIN 
cases c 
ON c.wcd_id = d.id
inner JOIN (SELECT MIN(id) AS min_id FROM cases GROUP BY reason_id) c2
ON c2.min_id = c.id

see http://sqlfiddle.com/#!9/fb4569/26

answered Jun 04 '20 at 22:33

nbk

45,398
8
30
47

What's WHERE got to do with it? – Strawberry Jun 05 '20 at 06:56
In the question theuser says tht he gets no results, when he has max and where 5684 the same happens when he uses Min and 5685. The query mutually excludes both possibility When you want to get 161 and 162 as a result you can only use MIN, brcause 5684 has no other lnumbers. but the the query check ervy available number 161 till 168 and gets his extremes. I thunk the query is wrong, but i don't know exactly what the user wants – nbk Jun 05 '20 at 09:46
Sorry; I didn't understand any of that. – Strawberry Jun 05 '20 at 09:49
there are two groups of numbers in wcd (161 and 162) and /163-168) the first group is exclusive linked to 5684 when you tell mysql that he shall only take rows from wcd that has the wid **5685**, that is link exclusive to the second group you never get the wanted result. That is why GMB answer is wrong I hope this is understandable – nbk Jun 05 '20 at 10:08

inner join with subquery results differs for these data sets

2 Answers2