MySQL query problem

[omoutop]

Hi all,

i am having a problem writing a complex query.
I have 800 records in a table.
One field has stored ids from another table.
The ids are in "2,34,11,56,78,45" format.
What is the best way to search these fields for an id sequence?
I mean, if i have the ids 4,6,8,12,45,34 and 1, that i wanna check, how can i do this?
I have try the LIKE syntax but dont work. Whenever i search for id LIKE '%1%' i also get 1,11,21,31,... (which is logical). How can i isolate the 1 only and leave out the 1x (11,12,13...21,31...) ids?

[proper_bo]

probably not the best way to do it but:

if your looking in a comma seperated string you could do

WHERE LIKE '1,%' OR LIKE '%,1,%' OR LIKE '%,1'

you then have all the options of if the 1 is at the start of the string, in the middle of the string or at the end of the string.

I await a better way to do it ;-)

[omoutop]

I am reading about the IN syntax... being a little confussed atm.... hope this works.

Thanks for the info bob but although it will work, i dont think it is practical since i can have up to 55 ids to check (x3 for your solution, gives 165 LIKE statements in my query ).

[proper_bo]

The in statement does a seperate call and then uses its results in another call. I don't think it related to your problem.

[txbakers]

IN is a good solution but it takes a little more planning to make work.

SELECT * FROM table WHERE id IN ('1','2','11','13','54')

brings those 5 results.

The IN syntax has an array of comma separated values inside parentheses.

[omoutop]

correct about the plannig...

i change my vars to var1,var2,.... (without the '') and the IN syntax did wonders :)

thanks all

[proper_bo]

I think I may have hold of the wrong end of the stick on this one.

You have a field that is a set of numbers seperated by commas?

The IN statement looks in an array and checks a single value from a field.

Or like I said, do I have the wrong end of the stick about your problem or the IN statement?