Error on Inner Join from Nested Select Statements I am trying to flatten an EAV style table. But I need to add a couple of columns from other tables. I read online that doing nested selects might be a good approach, though when I am getting an error on the last 2 joins (m6.meta_value and m7.meta_value) 
"Multi-part identifier could not be bound". I am using SQL Server 2016.

Does anyone know how I can still get my joins and work around this error?

SELECT distinct patients.ID , 
(SELECT meta_value FROM USERMETA m1 WHERE patients.ID = m1.USER_ID AND m1.meta_key = 'first') AS FirstName,
(SELECT meta_value FROM USERMETA m2 WHERE PATIENTS.ID = m2.USER_ID AND m2.meta_key = 'last') AS LastName,
(SELECT distinct meta_value FROM USERMETA m3 WHERE PATIENTS.ID = m3.USER_ID AND m3.meta_key = 'customer') AS CustomerID,

(SELECT meta_value FROM USERMETA m6 WHERE PATIENTS.ID = m6.USER_ID AND m6.meta_key = 'clinic') AS Clinic,
isnull(clinics.name, 'No Clinic') as 'Name',
(SELECT meta_value FROM USERMETA m7 WHERE PATIENTS.ID = m7.USER_ID AND m7.meta_key = 'doctorsid') AS DoctorID

from users patients
INNER JOIN user_group ON user_group.user_id = patients.ID AND user_group.group_id != 22 AND user_group.group_id = 5
INNER JOIN revision ON patients.ID = revision.wp_users_id AND revision.revision_types = 2
INNER JOIN doctors doc ON m7.meta_value = doc.bt_id  --"m7.meta_value" has error
INNER JOIN fronts ON m6.meta_value = fronts.id       --"m6.meta_value" has error       

group by patients.id
order by patients.ID
Some of the meta_key= values changed between updates, but you should still get the point of this.

Using conditional aggregation inside a common table expression to flatten out your EAV style table instead of subqueries:

;with patients as (
  select 
      p.ID
    , FirstName     = min(case when meta_key = 'first_name' then meta_value end)
    , LastName      = min(case when meta_key = 'last_name' then meta_value end)
    , CustomerID    = min(case when meta_key = 'customerid' then meta_value end)
    , PartnerClinic = min(case when meta_key = 'partner-clinic' then meta_value end)
    , DoctorID      = min(case when meta_key = 'doctorsid' then meta_value end)
  from users as p
    inner join user_group as ug
      on ug.user_id = p.id
     and ug.group_id != 22
     and ug.group_id = 5
    inner join revision as r
      on p.id = r.wp_users_id
     and r.revision_types = 2
    left join usermeta m
      on p.id = m.user_id
  group by p.id
)  
select 
    p.*
  , Name          = isnull(f.name, 'No Clinic') 
  , Doctor        = isnull(d.name, 'No Doctor')
from patients as p
  left join doctors as d
    on p.DoctorID = d.bt_id
  left join fronts as f
    on p.PartnerClinic = f.id
order by p.ID