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 ·
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 )
0 Likes 0 ·
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.
0 Likes 0 ·
Show more comments
Try the tweaked code (Original was by Kev Riley. I just extend that)
0 Likes 0 ·
Can you please let us know in which format the Comma-seperated list be supplied? Is it stored in a variable?
0 Likes 0 ·
@imrankasuri Please edit the original question rather than adding answers - it makes the thread hard to follow.
0 Likes 0 ·
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.