Mysql Left join without duplicate value from left table with the last value from it

Question

tbl_marketing

db_maid db_date     db_customer
1       01-04-2017  xxxx
2       05-04-2017  lll
3       08-04-2017  ggg

tbl_phonecall

db_id  db_mid db_due      db_nextdate  db_pnote
1       2     15-04-2017  16-04-2017   cccc
2       2     17-04-2017  18-04-2017   bbb
3       1     04-05-2017  16-05-2017   ghghh

this my query

select

  select
 marketing.*,
 phonecall.db_id,
 max(phonecall.db_due) db_due,
 max(phonecall.db_nextdate) db_nextdate,
 phonecall.db_pnote
 from tbl_marketing marketing
 left outer join tbl_phonecall phonecall
 on
 phonecall.db_mid=marketing.db_maid
 group by marketing.db_maid

The result i want is all rows from tbl_marketing even if there are Null values exists in the tbl_phonecall BUT NO DUPLICATE RECORDS with the last row from tbl_phoencall form the db_pnote,db_due associate to row in tbl_marketing order by the db_due from tbl_phonecall

My query didn't give me the exact result i want

the output will be like that

# Date        Customer dueDate    NextDate     pNote
2  05-04-2017 lll      17-04-2017  18-04-2017  bbbb
1  01-04-2017 xxxx     04-05-2017 16-05-2017   ccc
3  08-04-2017 ggg                              ghghh

Possible duplicate of [SQL Select only rows with Max Value on a Column](http://stackoverflow.com/questions/7745609/sql-select-only-rows-with-max-value-on-a-column) — Shadow, Apr 19 '17 at 13:17
max value is your latest date. Just need to add the left join to the mix. — Shadow, Apr 19 '17 at 13:19
@Shadow please check my query above i have create an update but i can't get the last note from tbl_phonecall — mohamad mohamad, Apr 19 '17 at 13:25
@mohamad_mohamad Which row to select if tbl_phonecall have several rows with the same `db_mid` and `db_due`? — Devart, Apr 19 '17 at 13:29
@mohamadmohamad pls check out the duplicate topic link I gave you, it explains what you need to do. You have not attempted to implement what's there. — Shadow, Apr 19 '17 at 13:30
@Devart i don't understand what you say but i want to show the last row enter in tbl_phonecall associate to tbl_marketing using db_mid in tbl_phonecall that contain the id of tbl_marketing — mohamad mohamad, Apr 19 '17 at 13:32
Think about situation when there are two rows with different `db_id`, but! `db_mid` = 2 and `dueDate` = 15-04-2017 in two of them. Which row will you select? — Devart, Apr 19 '17 at 13:44

score 1 · Accepted Answer · answered Apr 19 '17 at 13:44

You can use something like this:

 select  marketing.*,
         phonecall.db_id,
         phonecall.db_due,
         phonecall.db_nextdate,
         phonecall.db_pnote
 from tbl_marketing marketing
 left outer join tbl_phonecall phonecall on phonecall.db_mid=marketing.db_maid
     and phonecall.db_due=(select max(t2.db_due) from tbl_phonecall t2
                            where t2.db_mid=marketing.db_maid)
     and phonecall.db_nextdate=(select max(t3.db_nextdate) from tbl_phonecall t3
                            where t3.db_mid=marketing.db_maid)

score 0 · Answer 2 · answered Apr 19 '17 at 13:54

Are you looking to obtain all activity for the latest date in the Phone Call?

SELECT 
    m.*

    ,p.db_id
    ,p.db_due
    ,p.db_nextdate
    ,p.db_pnote

FROM tbl_marketing m

    LEFT JOIN
    (SELECT
            db_id
            ,max(Date) LDate
        FROM tbl_phonecall
        GROUP BY db_id
    ) LastDate
    ON LastDate.db_mid = m.db_maid

        LEFT JOIN tbl_phonecall p
        ON m.db_mid = m.db_maid
        AND LastDate.LDate = p.Date

Mysql Left join without duplicate value from left table with the last value from it

2 Answers2