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)
}

This is well summed up. Basically we have the default fields name and email, and we have the option to create custom fields, such as telephone, address, etc. We can have n fields. I'm trying to figure out a way to do a SELECT query that returns all fields without making use of subqueries or extra work outside the database (it's ok using joins though).

Example (telephone is a custom field):

TABLE person:

id    name       email
1     Test 1     [email protected]

---- 

TABLE field:

id    name
1     telephone
2     address

----

TABLE fieldPerson:

id    fieldId    personId    value
1     1          1           +1 555 555 555
2     2          1           First St.

----   

RESULTING QUERY

personId   name          email            telephone       Address
1          Test 1        [email protected]  +1 555 555 555  First St.

Is that possible?

Thanks.

EDIT:

If not possible, you may suggest a optimal solution using subqueries, only with SQL though.

Answer 1

So, depending on how much control you have over the query generation, if you can generate the SELECT columns in the same order as the PRIMARY KEY of field, you could try the following. I'm envisioning something where you know there's 2 keys in field, and what they are in advance (from an earlier query?).

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;

This gives me the result, given the sample data in the question:

personId | name   | email           | telephone      | Address   |
       1 | Test 1 | [email protected] | +1 555 555 555 | First St. |

This hinges on the notion that you've avoided the sub-query by doing an initial query earlier to know the arbitrary fields defined in the fields table. You didn't specify how dynamic this table is expected to be, so this may not be an unreasonable simplifying assumption to make.

Barring that, you can try something like:

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;

This gives me the same output without having to know the field id numbers, but I do need to know the names of the fields to generate the column names. The LIMIT is there to remove one of the JOIN products, and has a distinctly unholy feeling to it, I agree. I feel like there's not too much advantage to approach 2, since you have to know the column names anyways.

I would agree with @Mojtaba, however, that if you can't live with a pre-query to know the dynamic field names and ids, then I believe you're going to have to put sub-SELECT statements in there to pull those at query time.

Answer 2

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