ORM - Join table to get lookup values

I know you can join one cfc entity to another cfc entity, I’m not trying to do that.

I am trying to populate a column added to an entity (not persistent) with a string from a joined table’s column.  I’ve read everything about joincolumn, mappedby, elementColumn., and tried different combinations in order to put the correct parameters in the cfproperty tag but there doesn’t seem to be a solution for this simple thing.

Using an Address example.... instead of storing the state's name
in the address table, you would store the value state_id.  And for this example’s purpose you would have a lookup table called states

that only has two columns, the Primary key named id, and the label column called state_name.

TABLES:

States  (lookup table)
id - int  (PK)
state_name - string

Address table

street_number - string

street_name - string

state_id - int (FK)

zip_code - string

I would like to put in an additional field in Address entity called stateName
that will use its own state_id column value to join to the id column of the

States table and retrieve the value of the state_name field.

I am currently accomplishing this in the entities by using formula fields....
<cfproperty name="streetName" persistent = "false" formula = "select b.state_name from States b where b.id = state_id" />
and it works fine.

Every join example I run across has you create a complete entity for the joined table and join the cfc which seems like overkill since the lookup tables should not be updated with the entity and I do not want to have to make an enitty of something (lookup table) with just one column that is needed,

and then instead of address.getStateName I would have to do  address.getState().getStateName()

I have some entities that will have several of these joined lookup fields.
I just thought there would be a better way to simply define the join table, join fields in order
to do a simple join field so that you don’t have SQL code in entities that use lookup tables.

TIA

Ashar