I'm working with the following data structure:
| field| value | --- | --- | id | 123 | email| firstname.lastname@example.org
| field |value | --- |--- | parentId |123 -> users.id | name |Bank | value |WBC
Users have a one-to-many relationship to fields, a single user will have multiple fields with different names.
I'm trying to construct a query that would ultimately output something like:
| field | value |--- |--- | id |123 | email |email@example.com | bank |ANZ
So there's a new column added to the query for each unique name value. I do know all the possible name values up front.
My query is pretty standard so far, it's grouping but obviously grouping discards all but 1 of the joined model. Is there a way around this?
SELECT users.id, fields.name FROM users LEFT JOIN fields ON (fields.parent_id= users.id) GROUP BY users.id ORDER BY updated_at DESC LIMIT 100 OFFSET 0