CODEWITHSUNDEEP
sql-servercasting
Sunil
Sunil

Reputation: 13

how to use same column names with different data types in subquery in sqlserver

I have a query:

select * 
from proformaentry 
where cast(tcmno as varchar) not in(
    select tcmno from contreceipt
)

I want to list all tcmno from proformaentry table which is not in the table contreceipt. In proformaentry table, tcmno is int datatype but in contreceipt, it is varchar type. So I used above query, but it is not returning any rows.

Actually there are some tcmno in proformaentry table which is not present in contreceipt.

Hope I can get the correct query to retrieve the rows from here.

Upvotes: 1

Views: 310

Answers (1)

Felix Pamittan
Felix Pamittan

Reputation: 31879

I'm guessing contrecipt.tcmo is NULLABLE. NOT IN clause is the same as nested AND <> clauses. Example:

declare @tbl table(n int)
insert into @tbl values (1), (2), (3)

select * from @tbl where not n in(4, 5, 6, null)

The above query will not return any rows. If you transform the NOT IN, it'll be:

select * from @tbl where n <> 4 and n <> 5 and n <> 6 and n <> null

And since n <> null is FALSE, for the purpose of a WHERE clause, the NOT IN resulted to FALSE. In order to achieve the desired result, you can use NOT EXISTS:

select * 
from proformaentry p 
where not exists(
    select 1 from contreceipt where tcmno = cast(p.tcmno as varchar(20))
)

Upvotes: 1

Related Questions