Received: from vpcit.ru (qmailr@dialup.vpcit.ru [195.38.52.246]) by hub.org (8.10.1/8.10.1) with SMTP id e7P3Tv571160 for ; Thu, 24 Aug 2000 23:29:57 -0400 (EDT) Received: (qmail 27054 invoked from network); 25 Aug 2000 03:28:44 -0000 Received: from nt2.vpcit.ru (HELO vpcit.ru) (195.38.52.235) by dialup.vpcit.ru with SMTP; 25 Aug 2000 03:28:44 -0000 Message-ID: <39A5E7A4.E928FC2C@vpcit.ru> Date: Fri, 25 Aug 2000 09:27:32 +0600 From: Yury Don Organization: gercon X-Mailer: Mozilla 4.74 [en] (X11; U; Linux 2.2.15 i686) X-Accept-Language: ru, en MIME-Version: 1.0 To: pgsql-sql@postgresql.org Subject: Re: weird structure References: <200008241824.e7OIOR528066@hub.org> Content-Type: text/plain; charset=koi8-r Content-Transfer-Encoding: 7bit X-Archive-Number: 200008/272 rdg@viafractal.com.br wrote: > > Hi, > > 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 I did similar things using the following technique: in frontend I gather all attributes from person_attribute and then generate a query like this: select *, (select distinct d_value from person_data where d_person_id = person.p_id and d_attribute_id = 'here first attribute id'), (select distinct d_value from person_data where d_person_id = person.p_id and d_attribute_id = 'here second attribute id'), ... from person -- Sincerely yours, Yury