From: | Erik Jones <erik(at)myemma(dot)com> |
---|---|
To: | Andreas <maps(dot)on(at)gmx(dot)net> |
Cc: | Fernando Hevia <fhevia(at)ip-tel(dot)com(dot)ar>, pgsql-sql(at)postgresql(dot)org |
Subject: | Re: design of tables for sparse data |
Date: | 2007-11-13 15:33:23 |
Message-ID: | A86CD6DC-B41B-4389-A872-F503938FD5DC@myemma.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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
> 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)
You should look into the crosstab contrib package.
Erik Jones
Software Developer | Emma®
erik(at)myemma(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Aarni Ruuhimäki | 2007-11-14 11:06:34 | Originally created and last_mod by whom and when ? |
Previous Message | Andreas | 2007-11-13 02:10:44 | Re: design of tables for sparse data |