2

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
2

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
  • why do you use the min function? what if it is like a date column, would I then go use max for the most current? – choloboy Mar 17 '17 at 13:33
  • @choloboy There should only be 1 value per meta_key for a given user_id in a EAV or key-value pair. You could use min() or max() and it shouldn't make a difference. – SqlZim Mar 17 '17 at 13:35
0

Ended up using LEFT JOINS and joining on userid AND meta_key = 'myattribute' all the way through the query and GROUP BY id.

Ended up being fastest in SQL Server for me.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.