I don’t see any point to the idea of ENUMS; basically, it solves a problem that doesn’t exist.

There is nothing wrong with any of the techniques in the code below, other than the author not liking to write it.

I will say that you should never use a hard coded ID value; that defeats the whole purpose of a surrogate key by hard-coding the meaning of a particular ID.

--Method 1

select

      Order.OrderId,

      Order.OrderDate,

      Order.PaymentTypeId

from

      Order

      join

      PaymentType

      on Order.PaymentTypeId = PaymentType.PaymentTypeId

where

      PaymentTypeDescription = 'Invoice'

or

--Method 2

select

      Order.OrderId,

      Order.OrderDate,

      Order.PaymentTypeId

from

      Order

where

      Order.PaymentTypeId in (

      select

            PaymentType.PaymentTypeId

      from

            PaymentType

      where

            PaymentTypeDescription = 'Invoice'

      )

or

--Method 3

declare @PaymentTypeId int

select

      @PaymentTypeId = PaymentType.PaymentTypeId

from

      PaymentType

where

      PaymentTypeDescription = 'Invoice'

select

      Order.OrderId,

      Order.OrderDate,

      Order.PaymentTypeId

from

      Order

where

      Order.PaymentTypeId = @PaymentTypeId