question about WHERE IN

[lighton]

Hi,

 

Just a quick question, about WHERE IN()

 

I couldnt think of how to explain this so here goes

 

SELECT colunm1, colunm2, colunm3 FROM table WHERE colunm1 IN('$array1') AND colunm2 IN('$array2') AND colunm3 IN('$array3')

 

if

array1 = "1,2,3";

array2 = "4,5,6"'

array3= "7,8,9";

 

would i get any row that matched any of the values randomly for example a row that matched col1=2, col2=6 and col3=9,

 

or does it itterate through the array in order so i would only get a result where each colunm matched the first value in each array then the second value in each array and so on e.g. col1=1, col2=4 and col3=7 and so on. (this is what i am after, but i have a feeling that the result would be if any colunm value matched and value in each array.

 

Does anybody understand this and can you help! 

 

 

[Wildbug]

 

The values you put in the IN() function need to either be individually quoted if the column is a VARCHAR/string or not quoted if the column is numeric.  ...column1 IN ('1','2','3')...  -- or -- ...column1 IN (1,2,3)... -- not -- ...column1 IN ("1,2,3").

 

You will get all results that match your conditions.  That means that you will get all rows that have a 1,2, or 3 in column1 AND 4,5, or 6 in column2 AND 7,8, or 9 in column3.  So rows like (col1,col2,col3) (1,4,7),(1,5,9),(3,4, will match whereas (1,1,9) will not.  If you need them in any particular order, include an ORDER BY.

 

 

 

 

 

(Incidentally, the preferred spelling of "colunm" is actually "column.")

[lighton]

yep thats what i kinda expected would happen i wanted to tie each value in the array to each other in order, so value 1 of each array would have to be present in one row for it to be returned, then value 2 of each array and so on, it looks like the solution will have to be a loop, bad i know. thanks for yo help buddy. 

[fenway]

Yeah, normalize your columns... lists are EVIL.

[Wildbug]

If you just want order, use an "ORDER BY column1,column2,column3"