Re: ADVANCED: Help to find query to get 2 row values as colums
Nope, nothing about transactions here...
Anyway, this query does not work. It has one syntax error (apart from the different naming style), the "member # " bit is wrong, member should not be there.
Also, I think the join between profile X and property P is wrong. And a join to PropertyValues PV is missing. The join path starts at
MEMBER
down to PROFILE
up to PROPERTYVALUES
up to PROPERTY
Although you could write this using this "virtual table" approach, I think it's a lot easier when you write it like this:
mysql> SELECT m.member_id
-> , m.member_name
-> , hpv.propval_name as haircolour
-> , epv.propval_name as eyecolour
-> FROM members m
-> INNER JOIN profiles mh ON (m.member_id = mh.member_id)
-> INNER JOIN propertyvalues hpv ON (mh.propval_id = hpv.propval_id)
-> INNER JOIN properties hp ON (hpv.property_id = hp.property_id)
-> INNER JOIN profiles me ON (m.member_id = me.member_id)
-> INNER JOIN propertyvalues epv ON (me.propval_id = epv.propval_id)
-> INNER JOIN properties ep ON (epv.property_id = ep.property_id)
-> WHERE hp.property_name = 'haircolour'
-> AND ep.property_name = 'eyecolour'
-> ;
+-----------+-------------+------------+-----------+
| member_id | member_name | haircolour | eyecolour |
+-----------+-------------+------------+-----------+
| 1 | Alice | blonde | blue |
| 2 | Bob | black | green |
+-----------+-------------+------------+-----------+
2 rows in set (0.01 sec)
The ingredients are similar though: because you want to project columns for multiple similar items stored as rows, you need to repeat the similar bit that queries your column in your from clause, no matter if you are using inline views like David, or direct joins.
David is right where he sais that all these approaches will only work if you know exactly what properties you want to extract.
You can work around cases where a propertyvalue for a certain property might not exist for a particular member (use LEFT joins instead of INNER joins). But you can never get the query to automatically report any new propertyvalues and properties. The only way to do this, is to generate the query dynamically. That is, generate the string with as much columns as you need according to the number of properties in your properties table, and execute it dynamically with prepare and execute (manual 13.7). If that is what you like to do, i could give it a go ( i never did this with mysql before, but im confident that it can be done using the command line utility or the query browse).
I case your'e still reading, it is in fact possible to write it simpler (I mean, with less code) still (although it will probably perform worse):
select member_id
, member_name
, max(haircolour) as haircolour
, max(eyecolour) as eyecolour
from (
select m.member_id
, m.member_name
, case p.property_name when
'haircolour' then pv.propval_name
else ''
end as haircolour
, case p.property_name when
'eyecolour' then pv.propval_name
else ''
end as eyecolour
from members m
inner join profiles mp on m.member_id = mp.member_id
inner join propertyvalues pv on mp.propval_id = pv.propval_id
inner join properties p on pv.property_id = p.property_id
) pvs
group by member_name