Mysql join horizontal with vertical table

Question

I have the following structure:

CREATE TABLE person {
    id INT(11) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(30) NOT NULL,
    email VARCHAR(30) NOT NULL UNIQUE
}

CREATE TABLE field {
    id INT(11) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(30) NOT NULL
}

CREATE TABLE fieldPerson {
    id INT(11) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    fieldId INT(11) UNSIGNED,
    personId INT(11) UNSIGNED,
    value VARCHAR(30) NOT NULL,
    FOREIGN KEY (fieldId) REFERENCES field(id),
    FOREIGN KEY (personId) REFERENCES person(id)
}

It is well let down. Basically, we have default fields nameand email, and we have the ability to create custom fields, such as telephone, addressetc. We can have fields n . I am trying to find a way to make a query SELECTthat returns all fields without using subqueries or extra work outside the database (this is normal using connections, though).

Example (phone is a custom field):

TABLE person:

id    name       email
1     Test 1     [e-mail]

---- 

TABLE field:

id    name
1     telephone
2     address

----

TABLE fieldPerson:

id    fieldId    personId    value
1     1          1           [phone]
2     2          1           First St.

----   

RESULTING QUERY

personId   name          email            telephone       Address
1          Test 1        [e-mail]  [phone]  First St.

Is it possible?

Thank.

EDIT:

If this is not possible, you can offer the best solution using subqueries, but only with SQL.

Answer 1

SELECT p.*
     , group_concat(CASE f.name WHEN 'telephone' THEN fp.value END) telephone
     , group_concat(CASE f.name WHEN 'address'   THEN fp.value END) address
  FROM person p
  JOIN field f
    LEFT JOIN fieldPerson fp
      ON    fp.personId = p.id
        AND fp.fieldId = f.id
  GROUP BY p.id

Answer 2

, , , SELECT , PRIMARY KEY field, . , 2 field ( ?).

SELECT fp.personId,p.name,p.email,fp.value as telephone,fp2.value as Address 
    FROM fieldPerson fp INNER JOIN person p ON p.id = fp.personId 
                        INNER JOIN field f ON f.id = fp.fieldId 
                        INNER JOIN fieldPerson fp2 
    WHERE fp.fieldId = 1 and fp2.fieldId = 2;

, :

personId | name   | email           | telephone      | Address   |
       1 | Test 1 | [e-mail] | [phone] | First St. |

, , , , fields. , , , .

, - :

SELECT fp.personId,p.name,p.email,fp.value as telephone,fp2.value as Address 
    FROM fieldPerson fp INNER JOIN person p ON p.id = fp.personId 
                        INNER JOIN field f ON f.id = fp.fieldId 
                        INNER JOIN fieldPerson fp2 
    WHERE fp.fieldId != fp2.fieldId LIMIT 1,1;

, , . LIMIT , JOIN, , . , , 2, .

@Mojtaba, , , , , sub SELECT , .