Thread: weird structure
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
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 ----- From: <rdg@viafractal.com.br> To: <pgsql-sql@postgresql.org> Cc: <rdg@viafractal.com.br> Sent: Thursday, August 24, 2000 11:24 AM Subject: [SQL] weird structure > 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 >
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
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
> > 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
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