How can I do this with MySQL?

Question

I am using MySQL and have two database tables as follows:

Users

id username -------------- 1 Bill 2 Steve 

The objects

 user_id key value ---------------------- 1 AX 1 BY 1 CZ 2 AS 2 CT 

What query is required to get the following result?

 username ABC ------------------- Bill XYZ Steve ST 

I tried this with INNER JOIN , but in the end I got 5 lines (one for each corresponding line of the object).

Any help is greatly appreciated.

Answer 1

 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' 

Answer 2

If 'A' , 'B' and 'C' known in advance, 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 ; 

Answer 3

this may not be the request you are requesting, but it is the clean and sipmle request I 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 in the blanks. Thus, you can use the result set with two nested foreach (or any similar ones) and draw the desired table.