Mysql Left Join Last Record

user_8954396 0
Viewed 191
Tablename: call
-------------------
|id | name        |
-------------------
| 1 | a           |
| 2 | b           |
| 3 | c           |
| 4 | d           |
| 5 | e           |
-------------------

Tablename: call_detail 
--------------------------------
|id |   detail     | call_id  |
--------------------------------
|1  | note-1       |  1       |
|2  | note-2       |  1       |
|3  | note-3       |  2       |
|4  | note-4       |  2       |
|5  | note-5       |  2       |
|6  | note-6       |  3       |
--------------------------------

need result

----------------------
| id | name | detail |
| 1  | a    | note-2 | 
| 2  | b    | note-5 |
| 3  | c    | note-6 |
| 4  | d    | null   |
| 5  | e    | null   |
----------------------

how to left join two table last insert id

im need left join call.id = call_detail.call.id last record

Is there a way to do this

Can you show with a simple sampling

left-join join mysql
2 Answers

Do a join with a query of the call_detail table not with the entire table as you need to filter in the table before doing the join to be able to filter the table properly. You need to do it using a left join.

Use the following query:

SELECT c.id, c.name, cd.detail
  FROM calls c LEFT JOIN
  (SELECT 
        call_id, 
        MAX(id),
        detail
    FROM call_detail GROUP BY call_id) cd ON cd.call_id = c.id

You can see it working here: SQL Fiddle

NOTE: I used the name calls instead of call because of keywords.

user_24521060

You need to make a sub query and use it as a virtual table. Something like:

SELECT call.id, call.name, call_detail.detail
FROM call, 
LEFT JOIN ( select call_id, max(id) as last_call_id from call_detail group by call_id ) call_history ON call.id = call_history.call_id 
LEFT JOIN call_detail ON call_history.last_call_id = call_detail.id

EDIT: I added the left join :)

user_24522450
Related