In-Reply-To: <473907A4.4060400@gmx.net>
References: <0c8501c82575$bd3cf830$8f01010a@iptel.com.ar>
	<473907A4.4060400@gmx.net>
Mime-Version: 1.0 (Apple Message framework v752.3)
Content-Type: text/plain; charset=ISO-8859-1; delsp=yes; format=flowed
Message-Id: <A86CD6DC-B41B-4389-A872-F503938FD5DC@myemma.com>
Cc: Fernando Hevia <fhevia@ip-tel.com.ar>,
 pgsql-sql@postgresql.org
Content-Transfer-Encoding: quoted-printable
From: Erik Jones <erik@myemma.com>
Subject: Re: design of tables for sparse data
Date: Tue, 13 Nov 2007 09:33:23 -0600
To: Andreas <maps.on@gmx.net>


On Nov 12, 2007, at 8:10 PM, Andreas wrote:

> Fernando Hevia schrieb:
>>> --- Andreas Wrote: ---
>>> ...
>>>
>>> MY QUESTIONS:
>>>
>>
>> Your questions have a strong "home-work" look.
>
> Yes but I didn't want to bother everyone with my project's details.
> It's more like a CRM.
> Up until now I just tried to manage somehow with the sql basics and =20=

> now I like to get better.
> One interesting thing are crosstabs because I could use them in the =20=

> reporting module.
>
> I used this schoolbook scenario because it's such an easy =20
> example.   ;)
>
>
>>> 1)   How would I SELECT a report that looks like the first =20
>>> version of
>>> the pupil table out of the 3 table design?
>>> There must be a nontrivial SELECT statement that combines all 3 =20
>>> tables.
>>>
>>
>> You should check out the JOIN clause in select statements. Simple =20
>> example:
>>
>> Select t1.col1, t2.col1, t2.col2
>> >from t1 inner join t2 b on (t1.col1 =3D t2.col1)
>>
>
> A simple JOIN won't do the trick.
> That would give me something like:
> (42, Frank Miller,  Maths)
> (42, Frank Miller,  English)
> (42, Frank Miller,  Sports)
> (43, Suzy Smith,  Maths)
> (43, Suzy Smith,  History)
>
> But I want it turned around and a bit interpreted like:
>
> Column heads =3D (ID, Name, Maths, English, Sports, History)
> (42, Frank Miller, yes, yes, yes, no )
> (43, Suzy Smith, yes, no, no, yes)

You should look into the crosstab contrib package.

Erik Jones

Software Developer | Emma=AE
erik@myemma.com
800.595.4401 or 615.292.5888
615.292.0777 (fax)

Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com