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®
[EMAIL PROTECTED]
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
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
