Problem with join 1

[nohandlesleft254]

Hi,

I'm using the below query to get invoices from my DB. What I want to do is only get invoices that dont have a payment against them in the joined 'receipts' table (e.g. where receipts.invoicenr = ''). Yet i cant seem to arrange the query to give me that result. Any suggestions?

Thanks

PS: Effectively what i want to say is ON SALESINVOICES.SerNr <> RECEIPTSROW.InvoiceNr

<cfquery name="Ledgeractive" datasource="#Application.HansaData#">
SELECT
SALESINVOICES.SerNr,
SALESINVOICES.InvDate,
SALESINVOICES.CustOrdNr,
SALESINVOICES.Sum4,
SALESINVOICES.InvType,
SALESINVOICES.CustCode,
SALESINVOICES.OKFlag,
SALESINVOICES.PayDeal,
SALESINVOICES.PayDate,
RECEIPTSROW.PayDate AS Paydate2,
RECEIPTSROW.InvoiceNr,
RECEIPTSROW.RecVal,
RECEIPTSROW.CustCode
FROM RECEIPTSROW
LEFT JOIN SALESINVOICES
ON SALESINVOICES.SerNr=RECEIPTSROW.InvoiceNr
WHERE SALESINVOICES.CustCode = '#Session.CompanyID#'
AND (SALESINVOICES.InvDate LIKE '%#Session.LedgerQuickfind#%'
OR SALESINVOICES.SerNr LIKE '%#Session.LedgerQuickfind#%'
OR SALESINVOICES.CustOrdNr LIKE '%#Session.LedgerQuickfind#%'
OR SALESINVOICES.Sum4 LIKE '%#Session.LedgerQuickfind#%'
OR SALESINVOICES.InvType LIKE '%#Session.LedgerQuickfind#%')

ORDER BY
<cfswitch expression="#sort#">
<cfcase value="1">
CONVERT(DATETIME, SALESINVOICES.InvDate, 130) DESC
</cfcase>
<cfcase value="2">
CONVERT(DATETIME, SALESINVOICES.InvDate, 130) ASC
</cfcase>
<cfcase value="3">
CAST(SALESINVOICES.SerNr AS NUMERIC) DESC
</cfcase>
<cfcase value="4">
CAST(SALESINVOICES.SerNr AS NUMERIC) ASC
</cfcase>
<cfcase value="5">
SALESINVOICES.CustOrdNr DESC
</cfcase>
<cfcase value="6">
SALESINVOICES.CustOrdNr ASC
</cfcase>
<cfcase value="7">
CAST(SALESINVOICES.Sum4 AS NUMERIC) DESC
</cfcase>
<cfcase value="8">
CAST(SALESINVOICES.Sum4 AS NUMERIC) ASC
</cfcase>
<cfcase value="9">
SALESINVOICES.InvType DESC
</cfcase>
<cfcase value="10">
SALESINVOICES.InvType ASC
</cfcase>
</cfswitch>
</cfquery>

 

[okiiyama]

Is it sql server?

--Get Invoices where there is no receipt for that invoice

select si.*, rr.InvoiceNr
from SALESINVOICES si
left outer join RECEIPTSROW rr on si.SerNr = rr.InvoiceNr
where rr.InvoiceNr is null

 

[nohandlesleft254]

Thank you so much! - that was driving me nuts!