Help with a Custom Query

[chrislynch8]

Hi,

I'm trying to run the following query. What I want to acheive is that where they is no record matching the Where clause for the payments I want it to still include the opportunity and show 0 as the payment amount as it refers to payments taken this year. If I remove the Data part of the where clasue it returns the Opportunity with the Payment information for a Payment that is taken last year, but how can I show the Opportunity Information when there is nothing matching in the Payments?

SELECT o.NAME, oc.pr_account_no_c,p.payment_amount,pc.payment_date_c,p.payment_status,p.id 
FROM opportunities o  
	INNER JOIN opportunities_cstm oc ON o.id = oc.id_c  
	INNER JOIN accounts_opportunities ao ON o.id=ao.opportunity_id AND ao.deleted=0  
	INNER JOIN accounts a ON a.id=ao.account_id AND a.deleted=0 
	LEFT JOIN accounts_pr_pr_payments_c ap ON a.id = ap.accounts_p6053ccounts_ida 
	LEFT JOIN pr_pa_pr_payments p ON p.id = ap.accounts_pf9c2ayments_idb 
	INNER JOIN pr_pa_pr_payments_cstm pc ON p.id = pc.id_c 
WHERE oc.pr_account_no_c = '7801084' 
	AND p.payment_status = 'PAID' 
	AND (pc.payment_date_c >='2011-01-01 00:00:00' AND pc.payment_date_c<='2011-12-31 23:59:59')

[christianknoll]

Do a left join on the payments_cstm table as well (and in the future you might want to do this using standard vardefs extensions and not the sutdio ... ;-)

christian.

Hi,

I'm trying to run the following query. What I want to acheive is that where they is no record matching the Where clause for the payments I want it to still include the opportunity and show 0 as the payment amount as it refers to payments taken this year. If I remove the Data part of the where clasue it returns the Opportunity with the Payment information for a Payment that is taken last year, but how can I show the Opportunity Information when there is nothing matching in the Payments?

SELECT o.NAME, oc.pr_account_no_c,p.payment_amount,pc.payment_date_c,p.payment_status,p.id 
FROM opportunities o  
	INNER JOIN opportunities_cstm oc ON o.id = oc.id_c  
	INNER JOIN accounts_opportunities ao ON o.id=ao.opportunity_id AND ao.deleted=0  
	INNER JOIN accounts a ON a.id=ao.account_id AND a.deleted=0 
	LEFT JOIN accounts_pr_pr_payments_c ap ON a.id = ap.accounts_p6053ccounts_ida 
	LEFT JOIN pr_pa_pr_payments p ON p.id = ap.accounts_pf9c2ayments_idb 
	INNER JOIN pr_pa_pr_payments_cstm pc ON p.id = pc.id_c 
WHERE oc.pr_account_no_c = '7801084' 
	AND p.payment_status = 'PAID' 
	AND (pc.payment_date_c >='2011-01-01 00:00:00' AND pc.payment_date_c<='2011-12-31 23:59:59')