Trouble with SELECT

[warozzo]

If I have a table below:

-------+-----------+-----------+-------------------
       id  |  user_id  |   type    |      value
    -------+-----------+----------+--------------------
       1   |     2     | full_name | Fred Dobbs
       2   |     2     | email     | [e-mail]
       3   |     3     | full_name | Fred Durst
       4   |     3     | email     | [e-mail]
       5   |     4     | full_name | Freddy Krueger
       6   |     4     | email     | [e-mail]
       7   |     5     | full_name | Fred The Red
       8   |     5     | email     | [e-mail]
    -------+-----------+-----------+------------------

How do I query and has a result like:

    --------+----------------+---------------------
    user_id |   full_name    |      email
    --------+----------------+---------------------
       2    | Fred Dobbs     | [e-mail]
       3    | Fred Durst     | [e-mail]
       4    | Freddy Krueger | [e-mail]
       5    | Fred The Red   | [e-mail]
    --------+----------------+---------------------

I tried

SELECT * FROM `contact` WHERE user_id = (SELECT user_id FROM contact GROUP BY user_id)

but it return an error "#1242 - Subquery returns more than 1 row".

[laserlight]

You could try:

SELECT c1.user_id, c1.value AS full_name, c2.value AS email
    FROM contact c1 JOIN contact c2 ON c1.user_id=c2.user_id
    WHERE c1.type='full_name' AND c2.type='email';

[warozzo]

That's beautiful. Thank you @