Outer Join Problem
-
this is a sample account statement generation for an e-banking software Plz i want the accounts and customers details to appear even if no transactions exists. am finding it difficult making that work..i have tried full outer joins and outer joins any help? code below IF @type='RANGE' BEGIN print 'Query began' SELECT (SELECT Openbal FROM Accounts WHERE SUBSTRING(AccNum,9,4)=@AccNum) AS openBal,(SELECT Name FROM ThisCompany) AS CompName, (SELECT Branch FROM ThisCompany) AS Branch, (SELECT StreetAdd FROM ThisCompany) AS StreetAddress, (SELECT AddLine1 FROM ThisCompany) AS AddLine1, (SELECT AddLine2 FROM ThisCompany) AS AddLine2, @dt1 as Date1,@dt2 as date2, (SELECT Tel1 FROM ThisCompany) AS Tel, (SELECT Website FROM ThisCompany) AS Web, SUBSTRING(Accounts.AccNum,9,4) AS CustNum, Accounts.AccType, Accounts.Currency, Customers.FName+' '+ Customers.LName as FName, Customers.CorrespAdd, Transactions.Dt, Transactions.Debit, Transactions.Credit, Transactions.TranDetails, Transactions.Bal FROM Accounts FULL OUTER JOIN Customers ON Accounts.CustNum = Customers.CustNum FULL OUTER JOIN Transactions ON Accounts.AccNum = Transactions.AccNum WHERE SUBSTRING(Accounts.AccNUM,9,4)=@AccNum AND Transactions.Dt BETWEEN DATEADD(dd,1,@dt1) AND DATEADD(dd,1,@dt2) ORDER BY Transactions.Dt ASC RETURN END
-
this is a sample account statement generation for an e-banking software Plz i want the accounts and customers details to appear even if no transactions exists. am finding it difficult making that work..i have tried full outer joins and outer joins any help? code below IF @type='RANGE' BEGIN print 'Query began' SELECT (SELECT Openbal FROM Accounts WHERE SUBSTRING(AccNum,9,4)=@AccNum) AS openBal,(SELECT Name FROM ThisCompany) AS CompName, (SELECT Branch FROM ThisCompany) AS Branch, (SELECT StreetAdd FROM ThisCompany) AS StreetAddress, (SELECT AddLine1 FROM ThisCompany) AS AddLine1, (SELECT AddLine2 FROM ThisCompany) AS AddLine2, @dt1 as Date1,@dt2 as date2, (SELECT Tel1 FROM ThisCompany) AS Tel, (SELECT Website FROM ThisCompany) AS Web, SUBSTRING(Accounts.AccNum,9,4) AS CustNum, Accounts.AccType, Accounts.Currency, Customers.FName+' '+ Customers.LName as FName, Customers.CorrespAdd, Transactions.Dt, Transactions.Debit, Transactions.Credit, Transactions.TranDetails, Transactions.Bal FROM Accounts FULL OUTER JOIN Customers ON Accounts.CustNum = Customers.CustNum FULL OUTER JOIN Transactions ON Accounts.AccNum = Transactions.AccNum WHERE SUBSTRING(Accounts.AccNUM,9,4)=@AccNum AND Transactions.Dt BETWEEN DATEADD(dd,1,@dt1) AND DATEADD(dd,1,@dt2) ORDER BY Transactions.Dt ASC RETURN END
prubyholl wrote:
Plz i want the accounts and customers details to appear even if no transactions exists.
Replace the "Where" keyword with "And". The outer join fails because the where condtion executes after the completion of outer join. Hence you dont get any results. Whereas when you use an "And" clause , the condition is evaluated while the execution of the outer join. Also handle the null values gracefully when there is no transaction for a customer.
When you fail to plan, you are planning to fail.
-
prubyholl wrote:
Plz i want the accounts and customers details to appear even if no transactions exists.
Replace the "Where" keyword with "And". The outer join fails because the where condtion executes after the completion of outer join. Hence you dont get any results. Whereas when you use an "And" clause , the condition is evaluated while the execution of the outer join. Also handle the null values gracefully when there is no transaction for a customer.
When you fail to plan, you are planning to fail.