Re: ADVANCED: Help to find query to get 2 row values as colums

[David Powell]

Don't think of this as a complex query, think of it as three simple ones joined together.
If you had three tables Members, Members_eye, and Members_Hair and each one had id_member in it, you could easily do a three way join.

If there are a variable number of attributes for each person this technique is a little weak. But if as your example suggests all people have the same number of attributes then it's a go.


select member_id,
member_name,
haircolor,
eyecolor
from # virtual table HAIR -- This select statement looks like a table for the select above.
(select M.member_id, PV.name as haircolor
from member M, profile X, property P, PropertyValues PV
where M.member_id=X.member_id
and X.id_property = P.id_property
and P.name = "haircolor" )
AS Hair,
#Virtual Table Eyes -- This select statement looks like a table for the select on top.
(select M.member_id, PV.name as eyecolor
from member M, profile X, property P, PropertyValues PV
where M.member_id=X.member_id
and X.id_property = P.id_property
and P.name = "eyecolor" ) AS Eyes
member # real table Member
Where Hair.member_id = Member.Member_id
AND Member.Member_id = Eyes.member_id


There's an example of this in the MYSQL Reference 13.1.8.8