Using IN comparison operator for varchar fields

cracky
cracky used Ask the Experts™
on
I have a column in my table that contains a list of comma separated values corresponding to assorted id values from another field. None of the data types seem to handle an arbitrary array properly, so I have set the field type to VARCHAR(30).

My problem is that the IN() comparison operator does not seem to work properly in the field.

For example, in the people_id column, I have the following values: 24,45.

SELECT id from 'images' WHERE 24 IN(people_id);

Will return a value, but:

SELECT id from 'images' WHERE 45 IN(people_id);

Will return no results.

Any ideas as to choosing a better column type, so that IN() works as it should? Or even making the above query work.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
VGR

Commented:
I tried a lot of tests, but all have failed, even after looking at the online documentation.

I seems to work only with a constant enumeration (in the sense that it cannot be a field reference)

I tried a lot of thing, as you did I suppose, using quotes, concat(',') to build the "array", but to no avail.

I would suggest to forget the IN() idea. Let's see what type of data is better.

Hummm I would use a temporary table containing all the elements from your array ; this way you can select from it and perform a JOIN from you first table.
Guy Hengel [angelIII / a3]Billing Engineer
Most Valuable Expert 2014
Top Expert 2009

Commented:
The problem why the clause
24 IN ( people_id )
does not work while
24 in ( 24,25 )
works is: people_id is evaluated as the single string value "24,25".

A query that will work will not use the LIKE operator or needs to be "dynamic". The LIKE operator trick will be need a condition similar to this:
WHERE ',' + people_id + ',' LIKE '%,24,%'

Which will evalutat to
WHERE ',24,25,' LIKE '%,24,%'
which returns true and by thus the row(s) requested.

note that the LIKE '%something' will not be able to use ANY index, so you might experience this query to become the slower the more data you have in your table.

CHeers
>>> I have a column in my table that contains a list of comma separated values corresponding to assorted id values from another field.
Do you mean from another table instead of field ?

Do you have an images table and a person/people table and you have a many-many relationship between the two tables ie an image can contain/belong to many people and a person can be in/own many images ?  If so it's probably more in keeping with relation database concepts to create a third table "image_people".  This table merely contains a link between images and people instead of storing the comma separated values in the varchar column. For example

Table IMAGE

ID    INT
NAME  VARCHAR(30)

Table PERSON
ID    INT
NAME  VARCHAR(30)

TABLE PERSON_IMAGE
PERSON_ID    INT
IMAGE_ID     INT

You can then get an image ID belong to a particular person using

SELECT IMAGE_ID
FROM PERSON_IMAGE
WHERE PERSON_ID=24;

If you declare the link in the way that you have (comma separated list of values in a VARCHAR(30) column) not only do you have the problem you currently have but what happens if you need to store more ids in this field than will fit, you would probably change the column to be larger, but where will this end.  If you do it this way you will never have a limit on the number of ids you can have per image.

Hope this helps.

Author

Commented:
Thanks Gareth.

You are right, this is probably the best way to preserve integrity and get around my hurdle. Although in my database, it is not possible for any members other than the members entered in the initial insert to be associated with an image.

I am accepting Gareth's as my answer, thanks to VGR and angellll for their comments also.

I must say I am a little surprised that there is not a way to evaluate the values to the form that IN() requires via a function or the like. I spent a lot of time sorting through the available functions to look for one to no avail.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial