how to get only one row from right table for every row of left table MYSQL

Question

I want to join two tables but only want to get one row from the right table for every row of the left column. and the left table column should be the latest rows by DateTime. example if two entries are in the right table then only the latest one should join with the left table row.

I am getting the result I want from this query but I am not Making sure that it is correct and not random which may cause errors and incorrect data in the future.

my query

SELECT T2.search_id    , T2.user_id    , T2.activity
     , T2.activity_date, T2.message    , T2.message_title
     , T1.github_id    , T1.search_date, T1.followers
     , T1.location     , T1.following  , T1.createdAt
     , T1.language     , T1.updatedAt
FROM (SELECT `github_id`, `search_date`, `followers`,
             `location` , `following`  , `createdAt`,
             `language` , `updatedAt`  , email 
      FROM DATA) as T1
LEFT JOIN (SELECT ,user_id, activity     , activity_date
                 , message, message_title, search_id
           from MESSAGES
           where user_id = 1 
           order by activity_date desc) as T2 
       on T1.github_id = T2.search_id 
group by T1.github_id,
         T2.search_id

my query is something like this and is working please ignore any runtime error I just created this for example and for an explanation this is working fine. but just want to know if this correct and will not cause any problem

Does this answer your question? [Fetch the rows which have the Max value for a column for each distinct value of another column](https://stackoverflow.com/questions/121387/fetch-the-rows-which-have-the-max-value-for-a-column-for-each-distinct-value-of) — philipxy, Oct 13 '22 at 18:05

score 0 · Answer 1 · answered Oct 13 '22 at 15:30

Apart from getting your expected output or not (which can be evaluated only in presence of data), there are a couple of issues in your query:

you are grouping on two columns (namely "T1.github_id" and "T2.search_id"), though there are no aggregate functions in your SELECT clause: this may have potential unexpected behaviour, or just wrong/random output
you have a minor syntax error at the beginning of your SELECT clause for your T2 subquery: there's an extra comma that will make the DBMS fire an error.

how to get only one row from right table for every row of left table MYSQL

1 Answers1