MySQL Forums
Forum List  »  InnoDB

Re: ADVANCED: Help to find query to get 2 row values as colums
Posted by: David Powell
Date: June 14, 2005 01:05PM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: ADVANCED: Help to find query to get 2 row values as colums
1849
June 14, 2005 01:05PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.