MySQL Help

[Ngreth Thergn]

Is there an "in enum" command for slect?

I have an enum "food_type"

currently probably because of poor coding... items that do NOT have a food atribute are NOT set to null... so I cannot use the IS NOT NULL command, instead I need to actually check if it is in the enum.

Any such command?

[Tanliel]

I'm no MySQL guru, I just know where the documentation is...

MySQL Select Syntax:
http://www.mysql.com/doc/en/SELECT.html

And ENUM Type:
http://www.mysql.com/doc/en/ENUM.html

[Ngreth Thergn]

yup, been there. did not see any help :/

[KyrosKrane]

First off, let me say up front I've never worked with enums.

Is there a way to get a list of all possible values for an enum? For a table, you can use:

SELECT * FROM BigTable;

Is there an equivalent command that will just list the enum values?

If there is, you should be able to do something like this:

SELECT * FROM BigTable WHERE FieldOne IN (SELECT * FROM MyEnum);

or

SELECT * FROM BigTable WHERE FieldOne IN ([whatever command gives you the list of enum values]);

As a temporary work-around, you can do something like:

SELECT * FROM BigTable WHERE FieldOne IN ('Value1', 'Value2', 'Value3');

This only works if you have a very few values; more than five or so and it becomes inconvenient, not to mention defeating the purpose of the enum.

Edit: What data type is the FoodType column? If there's a distinctive value for the non-food items (e.g., zero), then maybe you can filter to exclude that.

[Ngreth Thergn]

The Data type is enum

I have tried the reverse filter and it did not work. It is either Null or "blank" (mostly blank... it should be NULL but for some reason is blank) I am not able to test for the blank condition for some reason. I tried food_type != 0 (as the documentation says) , I tried food type != "". Is maybe my NOT wrong?

[KyrosKrane]

Sorry, I'm just flailing around in the dark here.

Hum ... I remember an old trick from my DOS batch file programming days. (Ack! He's showing his age! ) I also use this trick sometimes to force a type change for data types, even in more recent languages. Try to concatenate random text with the values.

So it would be something like:

CONCAT(food_type, "banana") != "banana"

If food_type is an empty string, the expression resolves to "banana" != "banana", which resolves to false. If food_type is a text value, the entire expression resolves to true. If food_type is null, the expression resolves to null.

Also, depending on your server syntax, it might be <> instead of !=. At least, MS SQL uses <>.

Finally, one interesting tip I found (this is apparently MySQL specific) is the IFNULL function. Maybe you can use this to get around the problem of some values being NULL while others are not.

The syntax is:

IFNULL(value, result)

If "value" is null, the expression returns "result". If "value" is not null, it returns "value".

[Malathos]

I think your NOT is wrong

According to the doc - the enum can be null if specified, which is different from being set to a null string.
So you should be able to pull all the non-null, zero value rows by searching for either numerical value = 0 or = "".
If that works ok, then you should also be able to do a blanket update and set them all to null ...
update x set enum = null where enum = ""

(this is only a vague syntax - i'm an Oracle DBA, not a MySQL guru )

[Ngreth Thergn]

right. But I need the reverse. I need NOT. Basically I need to knwo when it is any item in the set... or when it is NOT null or NOT blank.

[Velvett]

You can compare ENUM members either by name or number:

Given the possible values of the ENUM field food_type as 'snack','meal','banquet' the following is true

SELECT food_type
FROM food_table
WHERE food_type='snack'

will return the same record set as

SELECT food_type
FROM food_table
WHERE food_type=1

It is possible to declare the empty string as a legal enumeration member. It will be assigned a non-zero numeric value, just as any other member listed in the declaration would be. However, using an empty string may cause some confustion because that string is also used for the error member that has a numeric value of 0.


I played around a bit with the ENUM field... paying attention to defaults. If I allowed the field to accept NULL s.. then NULL was set as the default value... else ... the first value in the enumeration member list was declared as the default. If you have values of both NULL and EMPTY STRING ... then we'd best hope that the field accepts NULL (duh Velvett) and that EMPTY STRING is one of the members of the enumeration list. If this is not true, then I think an adjustment to the table is in order.

Sticking with my example list above.. If I try to insert a value of 'enduring meal', that will cause the Error value to be inserted (enduring meal is not a memeber of the enumeration list).

Hmmm.. bouncing around here some what... sorry

Would this work

SELECT food_type
FROM food_table
WHERE food_type > 0

I'd expect that to return all records from the food_table that were correctly marked as being "food". If you need the records from that table that are NOT properly marked as food, then change your WHERE statement to

WHERE food_type < 1

Ngreth - for me to be of much futher assistance, I'd need to know the properties of that field... specifically.. what is the enumeration member list?





KyrosKrane -- Sub-selects as you've suggested are not available to MySQL as of yet. (Vague mention that maybe they will be available in release 5.0)


My hope is that I've been more helpful than confusing

[KyrosKrane]

Originally posted by Velvett
KyrosKrane -- Sub-selects as you've suggested are not available to MySQL as of yet. (Vague mention that maybe they will be available in release 5.0)

You mean this wouldn't work in MySQL?

SELECT * FROM BigTable WHERE FieldOne IN (SELECT SomeField FROM OtherTable);

Bother. I've done most of my SQL programming in MS SQL, so I don't know much about MySQL. OK, Ngreth, just ignore me then.

[Velvett]

Ayup.. that wont work

[Ngreth Thergn]

ok the food_type > 0 finally worked

Thanks!! you can see the results in the advanced search

[Ngreth Thergn]

Oh, BTW... MySQL V 4.1 +

http://www.mysql.com/doc/en/Subqueries.html

[KyrosKrane]

Ah, so sweet! Thankee, Ngreth! =)