Error on Inner Join from Nested Select Statements

Question

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

Answer 1

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

• choloboy: 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?
• SqlZim: @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.

Answer 2

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.

• RDFozz: Was this a version of SqlZim's answer? It sounds like it was....