无法在MySql中获得第三个表连接的不同和最大值

Question

Schemas 
// First table
CREATE TABLE assignments (
    id int,
    uid int,
    comments varchar(255),
    assignmentdate date,
    status int
);

INSERT INTO assignments (id, uid, comments, assignmentdate, status) 
values (1, 6, 'a', '2019-07-15', 0), (2, 6, 'ab', '2019-07-15', 0),
(3, 6, 'abc', '2019-07-14', 0), (4, 6, 'abc', '2019-07-14', 1)
, (5, 7, 'xyz', '2019-07-14', 1), (6, 7, 'zyx', '2019-07-14', 1);

// Second table
CREATE TABLE users (
    id int,
    username varchar(255),
    status int
);

INSERT INTO users (id, username, status) 
values (6, 'user1', 0), (7, 'user2', 0),
(8, 'user3', 1);

// Third table
CREATE TABLE user_images (
    id int,
    uid int,
    imagename varchar(255),
    status int
);

INSERT INTO user_images (id, uid, imagename, status) 
values (1, 6, 'abc.jpeg', 0), (2, 6, 'def.jpeg', 0), (3, 8, 'ghi.png', 1);

what I'm looking for here is to get 1) distinct and latest row of table assignments which, 2) joins the table users and get a row and then joins, 3) distinct and latest row of table user_images.

So far i have gone through this answer

My trial query:

SELECT
   p.*,
   u.username,
   groupedpi.*
FROM
   assignments p
INNER JOIN(
   SELECT
       comments,
       MAX(id) AS latest
   FROM
       assignments
   WHERE
STATUS
   = 0
GROUP BY
   uid
) AS groupedp
ON
   groupedp.latest = p.id
LEFT JOIN users u ON
   p.uid = u.id AND u.status = 0
LEFT JOIN(
   SELECT
       uid,
       MAX(id) AS latesti,
       imagename
   FROM
       user_images us
   WHERE
STATUS = 0
GROUP BY
   uid
   order by id desc LIMIT 1
) AS groupedpi
ON
   groupedpi.uid = p.uid 

Output:

The 3rd result I'm not getting, i.e I'm not getting the distinct and latest record of the third table while joining. Instead of abc.jpeg, I want to get def.jpeg.

Answer 1

MySQL is tripping you up here, because it automatically adds columns to GROUP BY if they aren't specified, so it's grouping the groupedpi subquery on imagename too - this will lead to duplicated rows. Remove the imagename column from the subquery (and the order by clause is irrelevant too) and have it just output the userid and the max image id

If you want the image name, join the images table in again on images.id = groupedpi.latesti (In the main query not the subquery that is finding the latest image id)

(Note that your screenshot says lastesti 2 but imagename abc- it's not the right pairing. ID 2 is def.jpg. When you want latest Id but also other data from the same row you can't do it in one hit unless you use an analytic (mysql8+) - you have to write a subquery that finds the max id and then join it back to the same table to get the rest of the data off that row)