How to use IN in SQL Server with Select

Question

SELECT CATEGORY 
FROM PEA_TEMPLATE  
WHERE TEMP_ID = 000001 AND TEMP_VERSION = 2

This query returns '000001','000002' which is saved in CATEGORY column as in this format.

SELECT * 
FROM HR_CATEGORY 
WHERE CAT_CODE IN ('000001', '000002')

This select is working fine with row string

SELECT * 
FROM HR_CATEGORY 
WHERE CAT_CODE IN (SELECT CATEGORY 
                   FROM PEA_TEMPLATE  
                   WHERE TEMP_ID = 000001 AND TEMP_VERSION = 2)

But when I use query inside IN it does not return any value.

What is the reason for this? Any way to fix this?

why you are doing this using sub query?? try joining these table select a.* from HR_CATEGORY a,PEA_TEMPLATE b where a.CAT_CODE =b.CATEGORY and b.TEMP_ID='000001' and b.TEMP_VERSION=2 — A_Sk, Mar 17 '15 at 05:37
This is not my actual table and only a demonstrate of this senario — Nishantha, Mar 17 '15 at 05:37
Are you querying on TEMP_ID or CAT_CODE? TEMP_ID you appear to be treating as a numeric. CAT_CODE appears to be a string. TEMP_ID IN(000001,000002) is different than CAT_CODE IN ('000001','000002') — Leptonator, Mar 17 '15 at 05:48
You need to look at your data types of: CATEGORY and CAT_CODE.. I suspect you may need to do a CAST/CONVERT on CATEGORY. — Leptonator, Mar 17 '15 at 05:54
Is your subquery returning a single row with the value "'000001','000002'" or two rows? — Peter Henell, Mar 17 '15 at 07:17
What is the data type of the `CAT_CODE ` from `HR_CATEGORY ` and `CATEGORY ` from `PEA_TEMPLATE ` ? And is this the actual query you are executing ? Or there is more ? And how many rows get return from the `SELECT CATEGORY FROM PEA_TEMPLATE WHERE TEMP_ID = 000001 AND TEMP_VERSION = 2` query. — Mahesh, Mar 17 '15 at 07:29
Does your `CATEGORY ` values `,` separated in `PEA_TEMPLATE ` ? — Mahesh, Mar 17 '15 at 07:31
@CoderofCode values saved as single string `'000001','000002'` — Nishantha, Mar 17 '15 at 08:35
Then you need to split that string into multiple values [Here is the link how to write function to split the string](http://sqlperformance.com/2012/07/t-sql-queries/split-strings). Write the function from any one of the approach and then use that in your query. — Mahesh, Mar 17 '15 at 08:37
@CoderofCode worked fine with `function` and return splitted string in multiple rows. Thanks all — Nishantha, Mar 17 '15 at 12:18

score 2 · Accepted Answer · answered Mar 17 '15 at 09:54

2

If CATEGORY can have concatenated strings with apostrophe e.g. '000001' or '000001','000002', then better to use LIKE with JOIN construction, like this:

SELECT * FROM HR_CATEGORY 
INNER JOIN 
  (SELECT CATEGORY FROM PEA_TEMPLATE WHERE TEMP_ID = 000001 AND TEMP_VERSION = 2) PEA
  ON PEA.CATEGORY LIKE '%'''+CAT_CODE+'''%'

answered Mar 17 '15 at 09:54

cypizek

327
2
6

Definitely one of solution for my question, Thanks @cypizek – Nishantha Mar 17 '15 at 12:14

score 1 · Answer 2 · edited Jun 20 '20 at 09:12

Your subquery is only returning a single row with the content:

"'000001','000002'"

For the IN clause to work each value must be separate as two rows:

'000001'

'000002'

You must split the value from the CATEGORY column into multiple strings. To do that you could use a string-splitting-function. There are many of them out there on the internet, here is a related question and answer T-SQL: Opposite to string concatenation - how to split string into multiple records

score 0 · Answer 3 · edited Mar 17 '15 at 05:55

0

Have you tried with trimming the values ?

TRIM()

SELECT * 
FROM HR_CATEGORY 
WHERE TRIM(CAT_CODE) IN (SELECT TRIM(CATEGORY) 
                   FROM PEA_TEMPLATE  
                   WHERE TEMP_ID = 000001 AND TEMP_VERSION = 2)

edited Mar 17 '15 at 05:55

A_Sk

4,532
3
27
51

answered Mar 17 '15 at 05:47

ps_prakash02

543
4
18

I used LTRIM and RTRIM. Nothing changed. – Nishantha Mar 17 '15 at 05:55
can't use join??? i already post a comment about it. see the joining query?? @Nishantha – A_Sk Mar 17 '15 at 05:59
what is the datatype of category column and cat_code ? – ps_prakash02 Mar 17 '15 at 06:11
In that way also I have to use IN() instead of where a.CAT_CODE =b.CATEGORY – Nishantha Mar 17 '15 at 06:15
what is the datatype of category column ? – ps_prakash02 Mar 17 '15 at 06:18
Have you tried casting both cat_code and category columns SELECT * FROM HR_CATEGORY WHERE CAST(CAT_CODE AS NVARCHAR(20)) IN (SELECT CAST(CATEGORY AS NVARCHAR(20)) FROM PEA_TEMPLATE WHERE TEMP_ID = 000001 AND TEMP_VERSION = 2) – ps_prakash02 Mar 17 '15 at 06:53

score 0 · Answer 4 · answered Mar 17 '15 at 06:15

0

Probably you have NULL category too. Try to add AND category IS NOT NULL to last WHERE.

answered Mar 17 '15 at 06:15

Matt

13,674
1
18
27

Actually `SELECT CATEGORY FROM PEA_TEMPLATE WHERE TEMP_ID = 000001 AND TEMP_VERSION = 2` return `'000001','000002'` – Nishantha Mar 17 '15 at 06:19

score 0 · Answer 5 · answered Mar 17 '15 at 06:18

0

Try to use CAST on CATEGORY in following:

SELECT * 
FROM HR_CATEGORY 
WHERE CAT_CODE IN (SELECT CAST(CATEGORY AS NVARCHAR(20)) 
                   FROM PEA_TEMPLATE  
                   WHERE TEMP_ID = 000001 AND TEMP_VERSION = 2)

answered Mar 17 '15 at 06:18

Stanislovas Kalašnikovas

9,203
4
32
59

score 0 · Answer 6 · edited Mar 17 '15 at 09:21

0

 go with FIND_IN_SET insted of in

i hope this will solve your problem

edited Mar 17 '15 at 09:21

Getz

3,983
6
35
52

answered Mar 17 '15 at 06:20

Affan

1,132
7
16

I think Find_IN_SET not supporting in SQL Server 2012 – Nishantha Mar 17 '15 at 06:27

How to use IN in SQL Server with Select

6 Answers6