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

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.

Answer 1

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
;