Hello

I've got a problem selecting values that match an option from a SET column.

My two columns are set up as follows:


And I'm currently using the following MySQL query:


Which turns out very strange resutls.

What I'm trying to do (in english) is to select records from the database which matches option 3 in SET column res_places AND matches option 1 in SET column res_places_re.

For some reason my SQL query does not give me the desired results. I know that a record matches both these criteria but yet this omits that record when returning result.

Is the SQL query I am using correct? Is this the correct way of going about what I'm trying to do? I don't want to use FIND_IN_SET because I won't always know the SET option name to pass to FIND_IN_SET.

Thanks for your help.

- wil

http://www.mysql.com/doc/S/E/SET.html...

in your example North -> 1, West -> 2, South -> 4, so selecting for 3 returns all records that contain North or West (or both).

(And not, as you might think South!)

Does that solve the problem?

Ivan
-----
Iyengar Yoga Resources / GT Plugins

Oh my gosh! Thank you. I now understand why I have been having such weird results.

My, that's a weird and annoying 'feature'.

So, is there a way of accessing my SET values numericaly 1,2,3,4 or not? The only way would be to actually use their value, as in "South" or "North"?

- wil

If I understand you correctly:

if you want to access "South" (i.e. the third element), then use "4".


Ivan
-----
Iyengar Yoga Resources / GT Plugins

Yes.

But there isn't a way of enabling the number to go 1,2,3,4,5,6? I can't force it to act this way?

It's trying to figure out the different permutations isn't it?

- wil

Well, it's not exactly permutations, it's powers:

your numbers -> mysql set numbers

1 -> 2^0
2 -> 2^1
3 -> 2^2

n -> 2^(n-1)

i.e. take your numbers (e.g. n=3), and take the (n-1)th power of 2 (e.g. 3-1 = 2, so you take 2 to the power 2 = 4).



Ivan
-----
Iyengar Yoga Resources / GT Plugins

I'm sure that's the formula for permutations as well. Never mind.

So there's no way of just being able to accees them in their numerical order. I guess I could get Perl to work out the correct number (using formula you provided), but it would be nice if this step could be avoided.

- wil