This is kinda related to my other question, but more related to SQL. So I split it up because I think it helps the community better to have the questions separated.
I often come across a situation where I have a 1:N relation, e.g. a table of items and another table with additional metadata / attributes for every item.
Consider this example:
users
+-----------+-----------+
| user_id | username |
+-----------+-----------+
| 1 | max |
| 2 | john |
| 3 | elton |
| 4 | tom |
| 5 | dave |
+-----------+-----------+
user_profile_data
+-----+-----------+-----------+
| uid | field_id | field_val |
+-----+-----------+-----------+
| 1 | 1 | a |
| 1 | 2 | b |
| 2 | 1 | c |
| 2 | 2 | d |
| 3 | 1 | e |
| 3 | 2 | f |
| 3 | 3 | g |
| 4 | 1 | h |
| 4 | 4 | i |
+-----+-----------+-----------+
Let's say the field_ids go from 1 to 10, not every field_id is defined for every user. Usually I select the users with all their additional data like this:
SELECT u.user_id, u.username, upd.field_id, upd.field_val
FROM users u
LEFT JOIN user_profile_data upd ON u.user_id = upd.uid
I get a row for every user/field combination and would need to "re-sort" in php because usually. Is there an efficient way in SQL to directly output a table which reorders the data to give me one row for every user and his additional data as separated columns?
+-----------+-----------+------------+------------+------------+------------+
| user_id | username | field_id_1 | field_id_2 | field_id_3 | field_id_4 |
+-----------+-----------+------------+------------+------------+------------+
| 1 | max | a | b | NULL | NULL |
| 2 | john | c | d | NULL | NULL |
| 3 | elton | e | f | g | NULL |
| 4 | tom | h | NULL | NULL | i |
| 5 | dave | NULL | NULL | NULL | NULL |
+-----------+-----------+------------+------------+------------+------------+
The only way I know would be to use SUBQUERIEs but I feel they are relatively slow especially when having a lot of field_ids. Apart from that I would need to know in advance the number of field_ids and would need to generate the SQL Query within PHP to contain a SUBQUERY for every field_id which is a bit ugly.
So I'd like to be able to use a generic approach like LEFT JOIN where the field_ids are not known beforehand.
And if there is no good and easy and efficient way: Why is it so? This comes to me as very common data structure / problem. So shouldn't this be built-in?
