Click here to Skip to main content
15,944,136 members
Please Sign up or sign in to vote.
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
Updated 22-Oct-13 2:34am
v4
Comments
ArunRajendra 22-Oct-13 4:16am    
Post some sample data

1 solution

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
 
Share this answer
 
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?

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900