pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Renato De Giovanni <rdg@viafractal.com.br>
To: pgsql-sql@postgresql.org
Subject: Re: weird structure
Date: Thu, 24 Aug 2000 19:15:35 -0300
Message-ID: <39A59E87.B882AB22@viafractal.com.br> (raw)
References: <200008241824.e7OIOR528066@hub.org>
	<000a01c00e0d$08429980$c330cc40@crusher>

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





view thread (6+ messages)  latest in thread

Message-ID: <39A59E87.B882AB22@viafractal.com.br>
Permalink:  ../39A59E87.B882AB22@viafractal.com.br/
Also on:    postgresql.org/message-id/39A59E87.B882AB22@viafractal.com.br

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: rdg@viafractal.com.br
  Subject: Re: weird structure
  In-Reply-To: <39A59E87.B882AB22@viafractal.com.br>
  References: <200008241824.e7OIOR528066@hub.org> <000a01c00e0d$08429980$c330cc40@crusher> <39A59E87.B882AB22@viafractal.com.br>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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