CODEWITHSUNDEEP
mysqlsql
abigail pattugalan
abigail pattugalan

Reputation: 3

mysql return one row of right table

I am facing a huge problem with MYSQL.

I have a table called tperson with the following content

+--------------+------------+  
| tperson_id   | first_name |  
+--------------+------------+  
| 1            | juan       |  
| 2            | miguel     |  
| 3            | Carlos     |  
| 4            | Diego      |  
+--------------+------------+

on the second table i have this data 

+--------------+------------+------------+  
| tperson_id   | trans_code | date_added |  
+--------------+------------+------------+
| 1            | 2000-01    |2020/03/03  |  
| 1            | 2000-02    |2020/03/04  |  
| 2            | 1999-05    |2019/12/25  |  
| 3            | 1999-06    |2019/12/26  |  
| 3            | 1999-07    |2019/12/27  |  
+--------------+------------+------------+

Now I want to have this result in mysql 

+--------------+------------+------------+------------+  
| tperson_id   | first_name | trans_code | date_added |  
+--------------+------------+------------+------------+  
| 1            | juan       |2000-02     | 2020/03/04 |  
| 2            | miguel     |1999-05     | 2019/12/25 |  
| 3            | Carlos     |1999-07     | 2019/12/27 |  
| 4            | Diego      | null       | null       |  
+--------------+------------+------------+------------+

what is the right MYsql statement to generation the result I want?

pls anyone help, I keep looking for the answer found nowhere. I am not good in any database.

thank you so much

Upvotes: 0

Views: 42

Answers (1)

Owl
Owl

Reputation: 6853

I'm assuming your 2nd table name is tdate, and data on trans_code and date_added that's being selected is the latest value if there are more than one data from the same tperson_id on table tdate

SELECT tp.tperson_id, tp.first_name, MAX(td.trans_code), MAX(td.date_added) 
  FROM tperson tp
  LEFT JOIN tdate td 
    ON tp.tperson_id = td.tperson_id 
 GROUP BY tp.tperson_id

Upvotes: 1

Related Questions