Return one row for a table Left join with another table has multirow with same ID

customers table:

enter image description here

notes table:

enter image description here

MY SQL query didn't work

SELECT * FROM customers LEFT JOIN notes  ON  customers.customer_id = notes.customer_id GROUP BY notes.customer_id 

I want to return one row for customer_id 277 : Mr,Kevin, New notes, Service, 2017-06-17 12:37:28 New note 2, Lead, 2017-06-17 15:04:42

customers table left join with multi tables

enter image description here

1 answer

  • answered 2017-06-17 19:10 scaisEdge

    You could use a group_concat on formatted result for notes

       select a.customer_id, a.ttle, a.first_name, group_concat(t.note_row)
       from customer
       inner join ( 
          select customer_id, concat(note, ' ', note_type, ' ', note_date_added ) as note_row
          from notes 
         ) t on t.customer_id = a.customer_id
        group by a.customer_id, a.ttle, a.first_name