I have 4 Tables. i.e.
crm_entry(id, prefix, firstname, lastname),
crm_phone(id, crm_id, extension,value),
sale_order(id, crm_id) and
I want a table like this..
| id | prefix | firstname | lastname | contact no | sales(count) |purchase (count)|
for getting data like this I tried inner join and left join to do so. but when Sales count is more than one.. the contact no gets duplicated in row as sales(count) times. same for purchase count. The query I used is written below:
select crm_entry.id, crm_entry.prefix, crm_entry.firstname, crm_entry.lastname, group_concate(concat(crm_phone.extension,'-',crm_phone.value)), count(sale_order.id), count(pur_order.id) from crm_entry inner join crm_phone on crm_entry.id = crm_phone.crm_id inner join sale_order on crm_entry.id = sale_order.crm_id inner join pur_order on crm_entry.id = pur_order.crm_id group by crm_entry.id
I want contact number only one time (contact can be multiple but not duplicate). How do I do?