Received: from vpcit.ru (qmailr@dialup.vpcit.ru [195.38.52.246]) by hub.org (8.10.1/8.10.1) with SMTP id e7S3oGL10295 for ; Sun, 27 Aug 2000 23:50:16 -0400 (EDT) Received: (qmail 27829 invoked from network); 28 Aug 2000 03:49:48 -0000 Received: from nt2.vpcit.ru (HELO vpcit.ru) (195.38.52.235) by dialup.vpcit.ru with SMTP; 28 Aug 2000 03:49:48 -0000 Message-ID: <39A9E12C.CE30AB78@vpcit.ru> Date: Mon, 28 Aug 2000 09:49:00 +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> <39A5E7A4.E928FC2C@vpcit.ru> <39A68D36.EADD6043@viafractal.com.br> Content-Type: text/plain; charset=koi8-r Content-Transfer-Encoding: 7bit X-Archive-Number: 200008/293 Renato De Giovanni wrote: > > > > 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 > > Yury... > That worked perfectly! Thank you very much! > > Tell me, I've never seen this sql construction before, is it specific to > postgres? Or should it also work on other databases? > > And is there any relevant performance issue considering this kind of > database schema and its peculiar sql commands? > > Thanks again! > -- > Renato > Sao Paulo - SP - Brasil > rdg@viafractal.com.br This construction works in any database which support subselect in target list in "select" statement, for example I used this in Interbase. What about performance - it strongly depends on amount of attributes. In order to maximize it you need to create indexes on all fields which participate in "where" clause - p_id, d_person_id, d_attribute_id. And performance . -- Sincerely yours, Yury