MySQL Query to determine if a "project" is "active"

I have a database with a few tables, laid out like this:

Projects table:
    PROJECTID   (int, primary key)
    TITLE            (varchar)
    STATUS       (varchar)

Example records:

PROJECTID         TITLE                                 STATUS
==================================================================
      1                Test project 1                        ACTIVE
      2                Project that went sour          CANCELLED
      3                Happy project                        FINISHED
      4                Soon to be happy project     AWAITINGPAYMENT


Enums table:
    ENUMTYPE                (varchar)
    ENUMVALUE             (varchar)
    ENUMCATEGORY     (varchar)

Example records:

ENUMTYPE                  ENUMVALUE           ENUMCATEGORY
==================================================================
PROJECTSTATUS       ACTIVE                       ACTIVE
PROJECTSTATUS       LEAD                          ACTIVE
PROJECTSTATUS       AWAITINGPAYMENT  ACTIVE
PROJECTSTATUS       CANCELLED               INACTIVE
PROJECTSTATUS       FINISHED                     INACTIVE
PAYMENTTYPE           VISA                            ""                  
PAYMENTTYPE           CASH                           ""                  
PAYMENTTYPE           CHQ                             ""                  
INVOICETYPE              ONLINE                        ""                  
INVOICETYPE              DELAYED                    ""                  
...etc


The project's status field will always be one of the values in the enums table where the ENUMTYPE property is "PROJECTSTATUS". So it will always be active/lead/awaitingpayment/cancelled/finished. Some of those statuses are considered "Active", others are "Inactive", as shown in the enumcategory field.

A project is defined as "active" if it's status has one of the values labelled flagged as "ACTIVE" in the enumcategory field of the enums table.

As an example, I can select all projects which are "Active" using a query like the following:

SELECT * FROM PROJECTS WHERE status IN
       (SELECT enumvalue
              FROM enums
              WHERE enumtype = 'PROJECTSTATUS' AND enumcategory = 'ACTIVE)




Here's what I need to do:

I want to create a view, called view_projects, which has a field in it called "Active". The field should be 0 if the project is inactive, and 1 if the project is active. How can I do that in a preferably somewhat optimized way?
Start Free Trial