* Jonathan Soons
> I have a table 'organisms' with a field 'category'
> of type SET('birds','bees','flowers','trees').
> A record could have none or more of these in that field.
> How do I select all records with both 'flowers' and 'trees'?
> I can "SELECT category,key FROM organisms;"
> Then I can test for the strings 'flowers' && 'trees' in the result set
> and then do another select for those id's but that's obviously
> the slow way.
A SET column is actually storing integers, each set value represent an
integer value, the powers of two. In your set
('birds','bees','flowers','trees') the following integers are used: birds=1,
bees=2, flowers=4 and trees=8.
To test for a combined value, you simply add the relevant integers. To get
rows with both 'flowers' and 'trees' and possibly other, you could do a test
if "category & 12 = 12". If you only want those with 'flowers' and 'trees'
and no other categories, test if "category = 12"
You can also use the LIKE oerator with the SET column, treating it as a
string. This is probably easier. It would be something like this:
SELECT * FROM organisms WHERE category LIKE "%flowers%trees%";
In this case you could have used LIKE "%flowers,trees", but the above syntax
is more scalable, you could add other categories to your SET later.
Only those with 'flowers' and 'trees' and no other categories:
... LIKE "flowers,trees";
(The order is relevant here, "trees,flowers" will not work.)
<URL: http://www.mysql.com/doc/en/SET.html >
--
Roger
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
