mysql - Compare enum indexes of two SQL entries -

mysql - Compare enum indexes of two SQL entries -

i need compare index of 2 enum values 2 similar columns in 2 different tables. enum values new, reviewed, design, production, , in ascending order of status in our process, should relatively easy compare based on thought thatnew == 1, reviewed==2, etc...

i trying:

select case when table1.review_status < table2.review_status table1.review_status when table2.review_status < table1.review_status table2.review_status end

this seems doing alphabetical compare on strings new, review, etc... need compare index, not value. possible?

add 0 enum value convert number numeric comparison.

https://dev.mysql.com/doc/refman/5.7/en/enum.html

create table table1 ( id int, review_status enum('new', 'reviewed', 'design', 'production') ); insert table1 values (1, 'new'), (2, 'production'), (3, 'design'), (4, 'reviewed'); create table table2 ( id int, review_status enum('new', 'reviewed', 'design', 'production') ); insert table2 values (1, 'design'), (2, 'new'), (3, 'reviewed'), (4, 'reviewed'); select table1.id, table1.review_status s1, table2.review_status s2, case when table1.review_status+0 < table2.review_status table1.review_status when table2.review_status+0 < table1.review_status table2.review_status end table1 bring together table2 on table2.id = table1.id;

http://www.sqlfiddle.com/#!2/906529/4

mysql sql enums