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.
All the datatype for the tables should match sequentially while joining the tables vertically using UNION or UNION ALL clause. Example: if first table has 3 columns with datatypes Int, Varchar and datetime then the next tables used in the union or union all clause should have 3 columns with the same datatype.
Ans: Joining two tables in SQL can be done in four major ways: Inner Join (returns rows with matching columns), Left Join (ALL records in the left table and matching records in the right table), Right Join (ALL records in the right table and matching records in the left table), and Union (removes duplicates).
It is possible to use multiple join statements together to join more than one table at the same time. To do that you add a second INNER JOIN statement and a second ON statement to indicate the third table and the second relationship.
If you'd like to get data stored in tables joined by a compound key that's a primary key in one table and a foreign key in another table, simply use a join condition on multiple columns.
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.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With