how to retrieve null values with join

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

I have used following query for retrieving values..

SQL

SELECT        VoucherTypes.specification, VoucherTypes.types, VoucherHead.VoucherDate, VoucherHead.VoucherNo, Accounts.AccountNo, Accounts.AccountName, 
                         CostAccounts.CostAccountName, VoucherDetail.VoucherDetail, VoucherDetail.Debit, VoucherDetail.Credit, VoucherHead.VoucherID, Accounts.AccountID
FROM            VoucherHead INNER JOIN
                         VoucherDetail ON VoucherHead.VoucherID = VoucherDetail.VoucherID INNER JOIN
                         VoucherTypes ON VoucherHead.VoucherType = VoucherTypes.vtypeid INNER JOIN
                         Accounts ON VoucherDetail.Accountid = Accounts.AccountID FULL OUTER JOIN
                         CostAccounts ON VoucherDetail.costaccount = CostAccounts.ID
WHERE        (VoucherTypes.types = @vtypes OR
                         @vtypes = '0') AND (VoucherDetail.VoucherDetail LIKE @voucherdetail OR
                         @voucherdetail = '0') AND (VoucherDetail.Debit = @debit OR
                         @debit = 0.0001) AND (VoucherDetail.Credit = @credit OR
                         @credit = 0.0001) AND (VoucherHead.VoucherDate BETWEEN @date1 AND @date2) AND (VoucherHead.Branchno = @branchid) AND 
                         (CostAccounts.CostAccountName LIKE @coststr OR
                         CostAccounts.CostAccountNo LIKE @coststr OR
                         VoucherDetail.costaccount IS NULL) AND (Accounts.AccountName LIKE @str OR
                         Accounts.AccountNo LIKE @str)

But This query does not show any row in which voucherdetail.costaccount is null...pls help me to solve this...

Posted 19-May-15 18:10pm

Nijisha Kc

Updated 19-May-15 18:33pm

PIEBALDconsult

v2

Add a Solution

Comments

PIEBALDconsult 20-May-15 0:36am

Start by reformatting so it's readable.
Then make sure your ANDs and ORs are separated properly.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Advay Pandya · Answer 1 · 2015-05-19T19:21:00

Solution 1

Hi Nijisha,

It's obvious that the result will not return with NULL values because SQL does not understand NULL value while comparing using equal operator (=).

Can you please try ISNULL while comparing in join ?

Something Like

SQL

CostAccounts ON Isnull(VoucherDetail.costaccount,0) = Isnull(CostAccounts.ID,0)

Please let me know if you have any concern or query or if I am missing something.

Thanks
Advay Pandya

Posted 19-May-15 19:21pm

Advay Pandya

Comments

Nijisha Kc 21-May-15 3:38am

i tried with isnull, but it doesnt work

Advay Pandya 22-May-15 4:47am

Okay, It should work. It may be possible that there are few other filters in your query (Like where condition,Inner join etc.) Is it possible those condition filter out the data you need ? Can you please share some demo data with me, so I can check ?
Sometimes we are looking at one place and issue lies on another place..