Received: from rush.u-netsys.com.br (rush.u-netsys.com.br [200.192.48.10]) by hub.org (8.10.1/8.10.1) with SMTP id e7OMZI521264 for ; Thu, 24 Aug 2000 18:35:18 -0400 (EDT) Received: (qmail 2818 invoked from network); 24 Aug 2000 22:35:13 -0000 Received: from ppp12-pm3.u-netsys.com.br (HELO viafractal.com.br) (200.192.49.141) by rush.u-netsys.com.br with SMTP; 24 Aug 2000 22:35:13 -0000 Message-ID: <39A59E87.B882AB22@viafractal.com.br> Date: Thu, 24 Aug 2000 19:15:35 -0300 From: Renato De Giovanni X-Mailer: Mozilla 4.72 [en] (Win95; I) X-Accept-Language: en MIME-Version: 1.0 To: pgsql-sql@postgresql.org Subject: Re: weird structure References: <200008241824.e7OIOR528066@hub.org> <000a01c00e0d$08429980$c330cc40@crusher> Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Archive-Number: 200008/270 Yes, Ryan, the idea is to use only one row with all attributes in it. The structure I described is easy to use when you want to know the attributes of a single person, and in this case your suggestion is the way to go - I knew that. I asked the question considering a specific person_id just to simplify. My real concern is about retrieving data from more than one person in a query. If attributes were all columns on "person" table, than a "select specific_fields from person where conditions=..." would do the job perfectly. But how could I achieve the same result using that different database schema? > Does it have to be in one row? > > Otherwise, assuming that person_data.d_person_id references person.a_id and > person_data.d_attribute_id references person_attribute.a_id: > > select a.a_name from person p, person_data d, person_attribute a where > p.p_name = 'UserYou'reLookingFor' AND p.p_id = d.d_person_id AND > d.d_attribute_id = a.a_id > > Would return a list of attributes the person has, one per row. > > ----- Original Message ----- > > Consider the following tables/fields: > > table "person": fields "p_id", "p_name". > > table "person_attribute": fields "a_id", "a_name". > > table "person_data": fields "d_person_id", "d_attribute_id", > > "d_value". > > > > Also consider that a person may not have data related to all possible > > attributes. > > > > Using this structure, how could I retrieve in one row with a single > > select statement all attributes from a person (showing null to > > attributes that were not registered to him)? > > > > Thanks for any suggestion, > > -- > > Renato > > Sao Paulo - SP - Brasil > > rdg@viafractal.com.br