CODEWITHSUNDEEP
mysqlsqljoinsubquery
P.N.
P.N.

Reputation: 351

SQL join two tables using the maximum values

I have these two tables A and B:

Table A:

|--------------|----------------|
|      ID_A    |other attributes|
|--------------|----------------|
|       1      |                |
|       2      |                |
|       3      |                |
|       4      |                |
|--------------|----------------|

Table B:

|--------------|----------------|----------------|----------------|
|      ID_B    |      ID_A      |  update_time   |other attributes|
|--------------|----------------|----------------|----------------|
|       1      |       2        |2017/01/01 07:00|                |
|       2      |       2        |2017/01/01 11:00|                |
|       3      |       2        |2017/01/01 13:00|                |
|       4      |       2        |2017/01/01 08:00|                |
|       5      |       2        |2017/01/01 06:00|                |
|       6      |       3        |2017/01/01 12:00|                |
|       7      |       3        |2017/01/01 13:00|                |
|       8      |       4        |2017/01/01 17:00|                |
|--------------|----------------|----------------|----------------|

Now I want to get the latest update time (from table B) for every row in table A. If there is no relation between table A and table B, I want to show NULL. The desired result for the upper tables is:

|--------|----------------|
|  ID_A  |  update_time   |
|--------|----------------|
|    1   |      NULL      |
|    2   |2017/01/01 13:00|
|    3   |2017/01/01 13:00|
|    4   |2017/01/01 17:00|
|--------|----------------|

Is there any way how to do this in SQL? Also some explanation would be fine. Thanks for any help!

Upvotes: 0

Views: 37

Answers (1)

Isaiah3015
Isaiah3015

Reputation: 491

Use a LEFT JOIN. This will return ONLY the matching values from Table B and keep all the values from Table A.

The subquery of LEFT JOIN will Return ALL values with ID_A and their MAX value of Update_DateTime. 

 Select a.ID_A
   b.Update_Time

FROM TABLE A A
LEFT JOIN (Select ID_A, max(Update_Time) as Update_Time
         FROM TABLE B
         Group by ID_A) B on B.ID_A = A.ID_A

Upvotes: 1

Related Questions