Error on Inner Join from Nested Select Statements
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