CODEWITHSUNDEEP
mysql
scampbell
scampbell

Reputation: 1000

Incorrect Use Of 'IN'?

I am trying to find all occurrences of a string in a field using the IN command but not returning any results when I know there should be some. I think the best way to show you is by example:

mysql> SELECT detail_sections FROM inet_staff_details WHERE detail_sections !='';
+-----------------+
| detail_sections |
+-----------------+
| 10,11           |
| 10,11           |
| 10,11           |
| 10,11           |
+-----------------+
4 rows in set (0.00 sec)

mysql> SELECT * FROM inet_staff_details WHERE 11 IN (detail_sections);
Empty set, 4 warnings (0.00 sec)

mysql> SELECT * FROM inet_staff_details WHERE '11' IN (detail_sections);
Empty set (0.00 sec)

As you can see, the number 11 is in the comma separated list but does not show up in the results. Please can you advise what I am doing wrong?

Cheers.

Upvotes: 0

Views: 62

Answers (5)

Jan S
Jan S

Reputation: 1837

What IN does, is to see if there is any value in the column (in this case detail_sections) which equals the specified value (in this case 11).

You should use LIKE to get the values that you want.
i.e.

SELECT * FROM inet_staff_details 
WHERE detail_sections LIKE '11,%'
   OR detail_sections LIKE '%,11' 
   OR detail_sections LIKE '%,11,%' 
   OR detail_sections = '11';

Upvotes: 2

Brian
Brian

Reputation: 6450

Yeah IN won't work for you here. IN is designed to pick out complete values, not substrings which is bascially what you've got here. Your problem is, that this is a basically flawed database design which makes searching for specific values such as 11 really difficult.

Your choices are

(a) redesign it a bit (read up on normalising databases, and joining tables), or

(b) search where: detail_sections = '11' or detail_sectoins LIKE '11,%' or detail_sections LIKE '%,11,%' or detail_sections LIKE '%,11'

Upvotes: 1

ravnur
ravnur

Reputation: 2852

Something like this:

   select * from inet_staff_details 
   where instr(concat(',', detail_sections, ','), ',11,') > 0

Upvotes: 0

Devart
Devart

Reputation: 122032

Try this query - 

SELECT * FROM inet_staff_details WHERE FIND_IN_SET(10, detail_sections);

FIND_IN_SET function.

Upvotes: 4

Aziz Shaikh
Aziz Shaikh

Reputation: 16534

Try this:

SELECT * FROM inet_staff_details 
WHERE detail_sections LIKE '%,11,%'
OR detail_sections LIKE '%,11'
OR detail_sections LIKE '11,%'
OR detail_sections = '11'

Upvotes: 0

Related Questions