Message-ID: <473907A4.4060400@gmx.net>
Date: Tue, 13 Nov 2007 03:10:44 +0100
From: Andreas <maps.on@gmx.net>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.0; de-AT;
	rv:1.8.1.9) Gecko/20071030 SeaMonkey/1.1.6
MIME-Version: 1.0
To: Fernando Hevia <fhevia@ip-tel.com.ar>
CC: pgsql-sql@postgresql.org
Subject: Re: design of tables for sparse data
References: <0c8501c82575$bd3cf830$8f01010a@iptel.com.ar>
In-Reply-To: <0c8501c82575$bd3cf830$8f01010a@iptel.com.ar>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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 now 
I like to get better.
One interesting thing are crosstabs because I could use them in the 
reporting module.

I used this schoolbook scenario because it's such an easy example.   ;)


>> 1)   How would I SELECT a report that looks like the first version of
>> the pupil table out of the 3 table design?
>> There must be a nontrivial SELECT statement that combines all 3 tables.
>>     
>
> You should check out the JOIN clause in select statements. 
> Simple example:
>
> Select t1.col1, t2.col1, t2.col2
> >from t1 inner join t2 b on (t1.col1 = 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 = (ID, Name, Maths, English, Sports, History)
(42, Frank Miller, yes, yes, yes, no )
(43, Suzy Smith, yes, no, no, yes)


Regards
Andreas