what to do if value of subquery returns blank in "NOT IN "

Question

0.00/5 (No votes)

See more:

below is my query

select distinct(Challan_No) from Challan_tb where Challan_No not exist (select Challan_No from Invoice_tb where Customer_ID =2 and InvYear=2013) and InvYear=2013 and Customer_ID =2

the sub query is returning blank so outer query not working

please Help

Posted 21-Oct-13 22:09pm

Omkaara

Updated 22-Oct-13 2:34am

ArunRajendra

v4

Add a Solution

Comments

ArunRajendra 22-Oct-13 4:16am

Post some sample data

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)

ArunRajendra · Answer 1 · 2013-10-21T22:21:00

Solution 1

Try the following query. Hope this what you need.

select distinct(Challan_No) from Challan_tb where Challan_No not in (select isnull(Challan_No,0) from Invoice_tb where Customer_ID =2 and InvYear=2013) and InvYear=2013 and Customer_ID =2

Posted 21-Oct-13 22:21pm

ArunRajendra

Updated 22-Oct-13 2:34am

v2

Comments

Omkaara 22-Oct-13 4:37am

not in also not working i tried

Omkaara 22-Oct-13 4:40am

if subquery return any value then its working ok
the problem is when subquery doesnt return any value outer query also does not works

ArunRajendra 22-Oct-13 4:48am

I tried this sample and returns 1,2 Challan_No. You can try it for yourself. Can you verify there is data in the table which satisfy the all the conditions.

declare @Challan_tb table
(
Challan_No int,
InvYear int,
Customer_ID int
)

declare @Invoice_tb table
(
Challan_No int,
InvYear int,
Customer_ID int
)

insert into @Challan_tb values(1,2013,2)
insert into @Challan_tb values(2,2013,2)
insert into @Invoice_tb values(3,2013,2)

select distinct(Challan_No) from @Challan_tb where InvYear=2013 and Customer_ID =2
and Challan_No not in (select Challan_No from @Invoice_tb
where Customer_ID =2 and InvYear=2013)

Omkaara 22-Oct-13 4:53am

insert into @Challan_tb values(1,2013,2)
insert into @Challan_tb values(2,2013,2)
insert into @Challan_tb values(3,2013,2)
insert into @Invoice_tb values(3,2013,2)

select distinct(Challan_No) from @Challan_tb where InvYear=2013 and Customer_ID =2
and Challan_No not in (select Challan_No from @Invoice_tb
where Customer_ID =2 and InvYear=2013)

above thing is working fine the problem occurs when there there is null data in @Invoice_tb

ArunRajendra 22-Oct-13 5:13am

What do you mean by null data? No records in @invoice_tb?

Omkaara 22-Oct-13 5:37am

insert into @Challan_tb values(1,2013,2)
insert into @Challan_tb values(2,2013,2)
insert into @Challan_tb values(3,2013,2)
insert into @Invoice_tb values( ,2013,2)
insert into @Invoice_tb values( ,2013,2)

the challan no is text field it can be blank in db

ArunRajendra 22-Oct-13 8:35am

Try the modified query in the solution.