I think I acompplish it! Merge that query into a view called report_1 and then:
(1) SELECT user_id, MAX(IF(name='RUT',value,NULL)) AS 'Rut', MAX(IF(name='Name',value,NULL)) AS 'Name' FROM report_1 GROUP BY user_id; +---------+-----------+--------+ | user_id | Rut | Name | +---------+-----------+--------+ | 1 | 170332881 | NULL | | 2 | 456465789 | felipe | +---------+-----------+--------+ Then: INSERT INTO data(user_id,field_id,value) values (1, 1, 'Rodrigo'); And then (1): +---------+-----------+---------+ | user_id | Rut | Name | +---------+-----------+---------+ | 1 | 170332881 | Rodrigo | | 2 | 456465789 | felipe | +---------+-----------+---------+ :) 2009/4/5 Rodrigo Aliste P. <rali...@gmail.com> > Hi Devs > > I have a tricky question and I want to know if its possible to accomplish > it on mysql and how. > > The following query: > > SELECT > fields.id AS field_id, > data.user_id, > fields.type, > fields.name, > data.value, > entries.ip, > entries.date, > entries.user_agent > FROM > data, > fields, > entries, > forms > WHERE > data.field_id = fields.id AND > data.user_id = entries.id AND > forms.id = fields.form_id AND > forms.id = entries.form_id AND > forms.id = 1 > ORDER BY > user_id, field_order > > Returns this: > > > +----------+---------+------+--------+-----------+-----------+---------------------+------------------+ > | field_id | user_id | type | name | value | ip | > date | user_agent | > > +----------+---------+------+--------+-----------+-----------+---------------------+------------------+ > | 2 | 1 | text | RUT | 17881 | 127.0.0.1 | 2009-03-08 > 14:27:51 | Unit Check | > | 1 | 2 | text | Name | felipe | 127.0.0.2 | 2009-03-08 > 14:28:35 | Mozilla | > | 2 | 2 | text | RUT | 456465789 | 127.0.0.2 | 2009-03-08 > 14:28:35 | Mozilla | > > +----------+---------+------+--------+-----------+-----------+---------------------+------------------+ > > And I want to display this by field_id and user_id like this: > > user_id RUT Name > 1 17881 > 2 456465789 felipe > > I know that if would be just one reference would be easy to do (by doing > IF(field_id=X,value,'')), I've also found the solution by application side > (by parsing columns first, rows later), > but it comes to troubles when I add a new fieldID or when a fieldID for > that userID its not found. > > Any idea? > > Thanks, > > Rod > > -- Rodrigo