question

imrankasuri avatar image
imrankasuri 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.
sql-server-2008
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

The question is unclear. Please edit it to clarify and provide expected output.
0 Likes 0 ·
Kev Riley avatar image
Kev Riley answered
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
12 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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.
0 Likes 0 ·
Instead of building a dynamic IN statement, use the underlying query as the source of the data for the right side of the join.
0 Likes 0 ·
i could not understand. can you please give some example ?
0 Likes 0 ·
@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.
0 Likes 0 ·
@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.
0 Likes 0 ·
Show more comments
SQL Kiwi avatar image
SQL Kiwi answered
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/

Ask.jpg (11.7 KiB)
5 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

The referenced article also includes a SQL CLR string-splitting function I wrote that is faster than the T-SQL function, not limited to 8000 characters, and works with both single-byte and double-byte character sets.
1 Like 1 ·
thank you very much sir, this is actually i wants. thanks for your time.
0 Likes 0 ·
@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.
0 Likes 0 ·
Feel free to mark it as the answer :)
0 Likes 0 ·
+1. This is one of the reasons, I asked the OP to read the article :)
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.