Query to select from multiple tables MySQL

Question

I have two tables. My task is to choose the last person comment.

db_user (db_user_id, name, balance)

db_comment (db_comment_id, db_user_id, text)

My query:

SELECT db_user.name,db_comment.text
FROM db_user INNER JOIN db_comment ON db_user.db_user_id = db_comment.db_user_id
ORDER BY db_comment.db_user_id DESC

Tried to use LIMIT but failed.

A table with values ​​has already been created here: http://sqlfiddle.com/#!9/badaf/14

My data sampling should receive the last comment (db comment.text) from each person (db_user.name).

Сondition, you cannot add new fields.

Answer 1

You can use the following solution using a additional JOIN:

SELECT dbu.name, dbc.text
FROM db_user dbu INNER JOIN (
  SELECT MAX(db_comment_id) AS db_comment_id, db_user_id
  FROM db_comment 
  GROUP BY db_user_id 
) dbc_max ON dbu.db_user_id = dbc_max.db_user_id
INNER JOIN db_comment dbc ON dbu.db_user_id = dbc_max.db_user_id 
  AND dbc.db_comment_id = dbc_max.db_comment_id
ORDER BY dbu.db_user_id DESC

... or using a sub-select directly on the SELECT:

SELECT dbu.name, (
  SELECT `text` 
  FROM db_comment dbc 
  WHERE dbu.db_user_id = dbc.db_user_id 
  ORDER BY dbc.db_comment_id DESC 
  LIMIT 1
) AS `text`
FROM db_user dbu
ORDER BY dbu.db_user_id DESC

demo on dbfiddle.uk