I am using MySQL and have two database tables as follows:
Users
id username -------------- 1 Bill 2 Steve Objects
user_id key value ---------------------- 1 A X 1 B Y 1 C Z 2 A S 2 C T What query is required to produce the following result?
username A B C ------------------- Bill X Y Z Steve S T I have tried this with an INNER JOIN, but end up with 5 rows (one for each corresponding object row).
Any help much appreciated.
If 'A', 'B', and 'C' are known beforehand, you can do this:
SELECT users.username, ( SELECT objects.value FROM objects WHERE objects.user_id = users.id AND objects.`key` = 'A' ) AS a, ( SELECT objects.value FROM objects WHERE objects.user_id = users.id AND objects.`key` = 'B' ) AS b, ( SELECT objects.value FROM objects WHERE objects.user_id = users.id AND objects.`key` = 'C' ) AS c FROM users ORDER BY users.username ; 
select u.username , oA.value A , oB.value B , oC.value C from users u left join objects oA on u.id = oA.user_id and oA.key = 'A' left join objects oB on u.id = oB.user_id and oB.key = 'B' left join objects oC on u.id = oC.user_id and oC.key = 'C' 
perhaps this is not the query that you are asking for, but this is a clean and sipmle query that I have used in your situation:
select objects.*, matrix.* from (select users.id, o.key from users, (select distinct key from objects) as o ) as matrix left join objects on matrix.id = objects.user_id and matrix.key = objets.key order by matrix.id, matrix.key This query "fills" the empty spaces. So you can consume the resultset with two nested foreach (or whatever similar) and draw the desired table.
Pivottable orCrosstabreport.