SQL Select With Multiple References to Single Table

Returning a column from each of the unique joins to the states:

select m.Name, s1.Enumeration as State1, s2.Enumeration as State2, s3.Enumeration as State3
from MasterTable m
left join StateTable s1 on m.State1 = s1.ID
left join StateTable s2 on m.State2 = s2.ID
left join StateTable s3 on m.State3 = s3.ID

Returning 1 column of all the states from the 3 joins:

select m.Name, ISNULL(s1.Enumeration + ',','') 
               + ISNULL(s2.Enumeration + ',','') 
               + ISNULL(s3.Enumeration,'') as Enumeration
from MasterTable m
left join StateTable s1 on m.State1 = s1.ID
left join StateTable s2 on m.State2 = s2.ID
left join StateTable s3 on m.State3 = s3.ID

There is also column-queries...

select m.Name,
 ISNULL((select Enumeration from StateTable where ID = m.State1),'') as State1,
 ISNULL((select Enumeration from StateTable where ID = m.State2),'') as State2,
 ISNULL((select Enumeration from StateTable where ID = m.State3),'') as State3
from MasterTable m

You need to use table aliases in order to join multiple copies of the same table:

   SELECT m.Name, 
          s1.Enumeration AS 'State1',
          s2.Enumeration AS 'State2'
     FROM MasterTable m
LEFT JOIN StateTable s1 = s1.id = m.state1
LEFT JOIN StateTable s2 = s1.id = m.state2

An INNER JOIN requires that data is present - if not, the entire record is excluded. A LEFT JOIN is safer, like if the state1/2/3/etc allows NULLs...