GajendraSinghParihar
GajendraSinghParihar

Reputation: 9131

Join exactly 1 record even if on condition match multiple records

Sample Query

SELECT count(A.ID) 
FROM `TABLE_A` A LEFT JOIN `TABLE_B` B ON (A.ID=B.ID) 
WHERE .....

I need:

Even if A.id match multiple records in Table B it should be consider as only one.

Actual Query

SELECT CampaignName,fsbcorponline.leads360campaigns.CostPerLead, 
count(fsbcorponline.leads360leads.id) leadCount,
count(CASE WHEN fsbcorponline.leads360leads.leadStatus like '%Contact Attempt%' OR                                
leadStatus like '%Call Back/Follow-up%' OR                                 
leadStatus like '%Working%' OR                                 
leadStatus like '%Credit Pulled%' OR                                 
leadStatus like '%Selected other Lender%' OR                                 
leadStatus like '%Pre-Approved%' OR                                 
leadStatus like '%Application Taken/Sent%' OR                                 
leadStatus like '%Application Received%' OR                                 
leadStatus like '%Submitted%' OR                                
leadStatus like '%Funded%' OR                                 
leadStatus like '%Future Opportunity%' OR                                 
leadStatus like '%Manager Review%' THEN 1 END ) leadContacted,         
COUNT(CASE WHEN fsbcorponline.leads360leads.lead_id IS NOT NULL THEN 1 END ) leadsToEncompass ,          
COUNT(CASE WHEN fsbcorponline.leads_to_loan.loan_closeDate IS NOT NULL AND loan_closeDate !='1900-01-01 00:00:00' THEN 1 END ) leadsToEncompassClose,         
COUNT(CASE WHEN fsbcorponline.leads_to_loan.loan_FundedDate IS NOT NULL AND loan_FundedDate !='1900-01-01 00:00:00' THEN 1 END ) leadsToEncompassFunded  
FROM (fsbcorponline.leads360leads LEFT JOIN fsbcorponline.leads360campaigns ON fsbcorponline.leads360leads.CampaignId=fsbcorponline.leads360campaigns.CampaignId)  
LEFT JOIN fsbcorponline.leads_to_loan ON fsbcorponline.leads360leads.id =lead_id  
Where 1=1 AND dateAdded between '11/24/2012 08:59:00' and '12/24/2012 08:59:00'AND CampaignName like '%TFSB.com%'

Upvotes: 0

Views: 138

Answers (2)

Mariappan Subramanian
Mariappan Subramanian

Reputation: 10083

try this distinct count will do,

SELECT count(distinct A.ID) 
FROM `TABLE_A` A JOIN `TABLE_B` B ON (A.ID=B.ID) 
WHERE .....

Since you are concerned about only matching records, simply JOIN will do and no need of LEFT JOIN

SELECT CampaignName,fsbcorponline.leads360campaigns.CostPerLead, 
count( distinct fsbcorponline.leads360leads.id) leadCount,
count(
CASE WHEN leadStatus like '%Contact Attempt%' OR                                
leadStatus like '%Call Back/Follow-up%' OR                                 
leadStatus like '%Working%' OR                                 
leadStatus like '%Credit Pulled%' OR                                 
leadStatus like '%Selected other Lender%' OR                                 
leadStatus like '%Pre-Approved%' OR                                 
leadStatus like '%Application Taken/Sent%' OR                                 
leadStatus like '%Application Received%' OR                                 
leadStatus like '%Submitted%' OR                                
leadStatus like '%Funded%' OR                                 
leadStatus like '%Future Opportunity%' OR                                 
leadStatus like '%Manager Review%' THEN 1 END ) leadContacted,         
COUNT
(CASE WHEN lead_id IS NOT NULL THEN 1 END ) leadsToEncompass ,          
COUNT(CASE WHEN loan_closeDate IS NOT NULL AND loan_closeDate !='1900-01-01 00:00:00' THEN 1 END ) leadsToEncompassClose,         
COUNT(CASE WHEN loan_FundedDate IS NOT NULL AND loan_FundedDate !='1900-01-01 00:00:00' THEN 1 END ) leadsToEncompassFunded  
FROM (fsbcorponline.leads360leads LEFT JOIN fsbcorponline.leads360campaigns ON fsbcorponline.leads360leads.CampaignId=fsbcorponline.leads360campaigns.CampaignId)  
LEFT JOIN fsbcorponline.leads_to_loan ON fsbcorponline.leads360leads.id =lead_id  
Where 1=1 AND dateAdded between '11/24/2012 08:59:00' and '12/24/2012 08:59:00'AND CampaignName like '%TFSB.com%'

Upvotes: 2

Salman Arshad
Salman Arshad

Reputation: 272446

A GROUP BY clause can be used for this purpose:

SELECT COUNT(A.ID) 
FROM `TABLE_A` A LEFT JOIN `TABLE_B` B ON (A.ID=B.ID) 
WHERE .....
GROUP BY A.ID

Upvotes: 2

Related Questions