Join multiple table with unique data

Question

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 purchase_order(id, crm_id)

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?

Answer 1

Ok, finally I got the mid-way to solve this problem.

I created views for contact number, sales order and purchase order along with the crm_entry and then I used these views in my stored procedure with inner joins. Here the query is :

SELECT
  view_crm_entry_phone.*,
  view_crm_entry_sale.sales,
  view_crm_entry_purchase.purchase
FROM
  view_crm_entry_phone
INNER JOIN
  view_crm_entry_sale ON view_crm_entry_sale.id = view_crm_entry_phone.id
INNER JOIN
  view_crm_entry_purchase ON view_crm_entry_purchase.id = view_crm_entry_phone.id