MySQL Forums
Forum List  »  InnoDB

ADVANCED: Help to find query to get 2 row values as colums
Posted by: Kevin Kopto
Date: May 26, 2005 07:35PM

I have big problems to find the right query to get the wanted result from a somewhat
advanced table structure. I am also not quite sure wether it's posssible at all
within current MySQL.

I have a table MEMBERS
I have a table PROPERTIES
I have a table POPERTYVALUES
I have a table PROFILES

There are 2 properties: haircolour and eyecolour stored in table PROPERTIES
Each property has different values stored in table POPERTYVALUES
Each member has 2 properties: haircolour and eyecolour stored in table PROFILES

So far so good... everything working fine

Now the problem: I need a query, that gives me ONE RECORDSET PER MEMBER, containing
all properties from the virtual profile



I HAVE:

table members
+-----------+-------------+
| member_id | member_name |
+-----------+-------------+
| 1 | Alice |
+-----------+-------------+
| 2 | Bob |
+-----------+-------------+

table properties
+-------------+---------------+
| property_id | property_name |
+-------------+---------------+
| 1 | haircolour |
+-------------+---------------+
| 2 | eyecolour |
+-------------+---------------+

table propertyvalues
+------------+-------------+--------------+
| propval_id | property_id | propval_name |
+------------+-------------+--------------+
| 1 | 1 | blonde |
+------------+-------------+--------------+
| 2 | 1 | red |
+------------+-------------+--------------+
| 3 | 1 | black |
+------------+-------------+--------------+
| 4 | 2 | brown |
+------------+-------------+--------------+
| 5 | 2 | blue |
+------------+-------------+--------------+
| 6 | 2 | green |
+------------+-------------+--------------+

profiles
+-------------+------------+
| member_id | propval_id |
+-------------+------------+
| 1 | 1 | => Alice has blonde hair
+-------------+------------+
| 1 | 5 | => Alice has blue eyes
+-------------+------------+
| 2 | 3 | => Bob has black hair
+-------------+------------+
| 2 | 6 | => Bob has green eyes
+-------------+------------+


I WANT:

+-----------+-------------+------------+-----------+
| member_id | member_name | haircolour | eyecolour |
+-----------+-------------+------------+-----------+
| 1 | Alice | blonde | blue |
+-----------+-------------+------------+-----------+
| 2 | Bob | black | green |
+--------- -+-------------+------------+-----------+


I suppose this is considered to be a very difficult and advanced query.
All combinations of UNION, JOIN and multiple subqueries didn't work.

Is there a MySQL expert who can help me, please?

Thanks in advance!

Kevin

Options: ReplyQuote


Subject
Views
Written By
Posted
ADVANCED: Help to find query to get 2 row values as colums
2725
May 26, 2005 07:35PM


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.