asked
Return ID not available in In query
Dear All, i have the following customer table tblcustomers. customer_id Customer Name 1. Ali1 2. Ali2 3. Ali3 if i use this query select * from tblcustomers where customer_id in (1,2) i will get two records if i use this query select * from tblcustomers where customer_id not in (1,4,5) i will get 2 and 3 customer number. but the situation is this i want to get back number 4 and 4 specified in in statement. can any body help how can i return those values which are specified in in query and not matched with the criteria (not the records matching to the criteria) ? how to do such kind of things? pleas help.
It sounds like what you want to get is more like the output from an outer join. You will not be able to get the result you want by using an IN clause declare @tblcustomers table (customer_id int, customer_name varchar(50)) insert into @tblcustomers select 1, 'Ali1' insert into @tblcustomers select 2, 'Ali2' insert into @tblcustomers select 3, 'Ali3' select TableInsteadOfINClause.CustomerID, tblcustomers.customer_name from @tblcustomers tblcustomers right join (select 1 as CustomerID union select 4 union select 5) as TableInsteadOfINClause on TableInsteadOfINClause.CustomerID = tblcustomers.customer_id gives CustomerID customer_name ----------- ------------------ 1 Ali1 4 NULL 5 NULL
how to write a generic query if the id's in the IN Statement are more than 1000? that can return only the id's against which the records are not available? i will be really thankful to you.
Instead of building a dynamic IN statement, use the underlying query as the source of the data for the right side of the join.
@Kev Riley I thought of it as well. But then scratched my head that it is not possible with one statement. The results of this script are neither against IN (1,2) clause nor against NOT IN (1,4,5) clause. Hope I am making sense.
@Usman yes I agree, but I'm not sure of the OP's original intention - just wanted to demonstrate that to get results that "don't match", you would use an outer join.
I agree. It is really ambiguous. Perhaps this may be what the OP wants. declare @tblcustomers table (customer_id int, customer_name varchar(50)) insert into @tblcustomers select 1, 'Ali1' insert into @tblcustomers select 2, 'Ali2' insert into @tblcustomers select 3, 'Ali3' SELECT Customer_ID , tblcustomers.customer_name FROM @tblcustomers tblcustomers WHERE [customer_id] NOT IN ( SELECT CustomerID FROM ( SELECT 1 AS CustomerID UNION SELECT 4 UNION SELECT 5 ) AS TableInsteadOfINClause ) UNION ALL SELECT CustomerID , NULL AS CUSTOMERNAME FROM ( SELECT 1 AS CustomerID UNION ALL SELECT 4 UNION ALL SELECT 5 ) AS TableInsteadOfINClause WHERE [CustomerID] NOT IN ( SELECT [Customer_ID] FROM @tblcustomers )
actually i want to get back those customer id's specified in brackets of the in statements but does not exists in the actual customer table. sorry sir, i still failed to get the idea to make a generic query. for example look at this query select * from tblcustomers where customer_id not in (1,4,5,6,7,8,9,10,11,12,13,14,15) the result should be like this. 4 5 6 7 8 9 10 11 12 13 14 15 the query will return those id's specified in ( in statement comma separated values) but not exists in the actual table.
Try the tweaked code (Original was by Kev Riley. I just extend that)
Can you please let us know in which format the Comma-seperated list be supplied? Is it stored in a variable?
This produces the output you seem to be looking for: ![Sample output][1] DECLARE @Customers AS TABLE ( customer_id integer PRIMARY KEY, customer_name varchar(50) ); INSERT @Customers (customer_id, customer_name) VALUES (1, 'Ali1'), (2, 'Ali2'), (3, 'Ali3'); DECLARE @CustomerCSV varchar(8000) = '1,4,5,6,7,8,9,10,11,12,13,14,15'; SELECT CustomerID = dsk.ItemNumber FROM dbo.DelimitedSplit8K(@CustomerCSV, ',') AS dsk WHERE NOT EXISTS ( SELECT * FROM @Customers AS c WHERE c.customer_id = dsk.ItemNumber ); That sample uses a generic string-splitting function: -- General utility string-splitting function CREATE FUNCTION dbo.DelimitedSplit8K ( @pString VARCHAR(8000), @pDelimiter CHAR(1) ) RETURNS TABLE WITH SCHEMABINDING AS RETURN WITH E1(N) AS ( SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 ), E2(N) AS (SELECT 1 FROM E1 a, E1 b), E4(N) AS (SELECT 1 FROM E2 a, E2 b), cteTally(N) AS ( SELECT 0 UNION ALL SELECT TOP (DATALENGTH(ISNULL(@pString,1))) ROW_NUMBER() OVER (ORDER BY (SELECT 1)) FROM E4 ), cteStart(N1) AS ( SELECT t.N+1 FROM cteTally AS t WHERE (SUBSTRING(@pString,t.N,1) = @pDelimiter OR t.N = 0) ) SELECT ItemNumber = ROW_NUMBER() OVER(ORDER BY s.N1), Item = SUBSTRING(@pString,s.N1,ISNULL(NULLIF(CHARINDEX(@pDelimiter,@pString,s.N1),0)-s.N1,8000)) FROM cteStart AS s; GO Function adapted slightly from an [SSC article][2] by @Jeff Moden [1]:
http://ask.sqlservercentral.com/storage/temp/16-Ask.jpg [2]:
http://www.sqlservercentral.com/articles/Tally+Table/72993/
thank you very much sir, this is actually i wants. thanks for your time.
@SQL Kiwi the same was in my mind. +1 from here. @imrankasuri Please keep in mind that this posted function can handle only 4000 comma separated values at the most. If you want more values to be sorted out then you should read the aforementioned great article for more details.
+1. This is one of the reasons, I asked the OP to read the article :)
5 People are following this question.
