pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedweird structure
6+ messages / 4 participants
[nested] [flat]
* weird structure
@ 2000-08-24 18:24 rdg@viafractal.com.br
2000-08-24 20:51 ` Re: weird structure Ryan Williams <shrewsbury@louisiana.com>
2000-08-25 03:27 ` Re: weird structure Yury Don <yura@vpcit.ru>
0 siblings, 2 replies; 6+ messages in thread
From: rdg@viafractal.com.br @ 2000-08-24 18:24 UTC (permalink / raw)
To: pgsql-sql; +Cc: rdg@viafractal.com.br
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: weird structure
2000-08-24 18:24 weird structure rdg@viafractal.com.br
@ 2000-08-24 20:51 ` Ryan Williams <shrewsbury@louisiana.com>
2000-08-24 22:15 ` Re: weird structure Renato De Giovanni <rdg@viafractal.com.br>
1 sibling, 1 reply; 6+ messages in thread
From: Ryan Williams @ 2000-08-24 20:51 UTC (permalink / raw)
To: pgsql-sql; +Cc: 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
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: weird structure
2000-08-24 18:24 weird structure rdg@viafractal.com.br
2000-08-24 20:51 ` Re: weird structure Ryan Williams <shrewsbury@louisiana.com>
@ 2000-08-24 22:15 ` Renato De Giovanni <rdg@viafractal.com.br>
0 siblings, 0 replies; 6+ messages in thread
From: Renato De Giovanni @ 2000-08-24 22:15 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: weird structure
2000-08-24 18:24 weird structure rdg@viafractal.com.br
@ 2000-08-25 03:27 ` Yury Don <yura@vpcit.ru>
2000-08-25 15:13 ` Re: weird structure Renato De Giovanni <rdg@viafractal.com.br>
1 sibling, 1 reply; 6+ messages in thread
From: Yury Don @ 2000-08-25 03:27 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: weird structure
2000-08-24 18:24 weird structure rdg@viafractal.com.br
2000-08-25 03:27 ` Re: weird structure Yury Don <yura@vpcit.ru>
@ 2000-08-25 15:13 ` Renato De Giovanni <rdg@viafractal.com.br>
2000-08-28 03:49 ` Re: weird structure Yury Don <yura@vpcit.ru>
0 siblings, 1 reply; 6+ messages in thread
From: Renato De Giovanni @ 2000-08-25 15:13 UTC (permalink / raw)
To: pgsql-sql
> > 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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: weird structure
2000-08-24 18:24 weird structure rdg@viafractal.com.br
2000-08-25 03:27 ` Re: weird structure Yury Don <yura@vpcit.ru>
2000-08-25 15:13 ` Re: weird structure Renato De Giovanni <rdg@viafractal.com.br>
@ 2000-08-28 03:49 ` Yury Don <yura@vpcit.ru>
0 siblings, 0 replies; 6+ messages in thread
From: Yury Don @ 2000-08-28 03:49 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2000-08-28 03:49 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-08-24 18:24 weird structure rdg@viafractal.com.br
2000-08-24 20:51 ` Ryan Williams <shrewsbury@louisiana.com>
2000-08-24 22:15 ` Renato De Giovanni <rdg@viafractal.com.br>
2000-08-25 03:27 ` Yury Don <yura@vpcit.ru>
2000-08-25 15:13 ` Renato De Giovanni <rdg@viafractal.com.br>
2000-08-28 03:49 ` Yury Don <yura@vpcit.ru>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox