So background is clientnumber is value that client sent to me and will be inserted into the database. But so the 3 numbers above aren't inserted in the database by me YET, so it will turn blank when I search them in database. Is there way for SQL to show those numbers as null value?
If I don't know the 3 above values, how can I show them (since they are null value when searched) from the value that does exist?
Maybe one to do this is using not IN with client numbers that I have already inserted? But let's say there are 2000 numbers I inserted, it would be very inefficient. What would be the best way to do this?
Let's say the below are the values that I know, but two of them are null, how do I only show the null values?
select *
from dataentry with (nolock)
where clientnumber in (
'00602',
'00897',
'00940',
'22234',
'87669'
)
I think you can use right join
--Let your table only contain these three number 00602,00897,00940
Create Table #temp
(
clientnumber varchar(10)
)
INSERT INTO #temp
values
('00602'),
('00897'),
('00940')
--Here is your values that you want to test
Declare @table table
(
clientnumber varchar(10)
)
INSERT INTO @table
values
('00602'),
('00897'),
('00940'),
('22234'),
('87669')
--the following shows the values that does not exists on your table
select ta.clientnumber
from #temp as tm
right join @table as ta
on tm.clientnumber =ta.clientnumber
where tm.clientnumber is null
--the following shows all values and there are two null values due to no any match on your table
select tm.clientnumber
from #temp as tm
right join @table as ta
on tm.clientnumber =ta.clientnumber
DROP TABLE #temp