Re: cross tab confusion - Mailing list pgsql-general

Thanks for your response.

> >
> > I'm building a pupil assessment record. I have
> these
> > two tables:
> >
> > table `pupil`
> >
> > pupil_id   name
> > 1          jeff
> > 2          fred
> > 3          rita
> >
> >
> > table `reading_level`
> >
> > record_id   pupil_id   week   level
> > 1           1          1      6.1
> > 2           2          1      4.3
> > 3           1          2      6.2
> > 4           2          2      4.4
>
>
> Does that record_id column do anything except give
> you a unique key?

Yes, I see what you're saying. It is, as you suggest,
redundant.

> Unless it relates to something in the real world, it
> is redundant,
> because (pupil_id, week) is the obvious primary key
> -- assuming you have
> a new table for each academic year...

I'd probably not want to have a new table for each
year would I? That would mean this database could only
continue to run while this school employed me! Not
many primary school teachers are database admins. I'd
want this to stand the test of time? Is that possible?
Is it too ambitious to expect this to run once it's
set up? Run and run?

> > I'd really need to present this data thus:
> >
> > name   week1   week2   week3
> > jeff   6.1     6.2
> > fred   4.3     4.4
> >

I came up with this (and I was proud! I only installed
this last week)...

The static query:

SELECT distinct test.name,a1.level AS l1,a2.level AS
l2,a3.level AS l3, a4.level AS l4
FROM `test`
LEFT JOIN `test` AS `a1` ON a1.date = 1 AND a1.name =
test.name
LEFT JOIN `test` AS `a2` ON a2.date = 2 AND a2.name =
test.name
LEFT JOIN `test` AS `a3` ON a3.date = 3 AND a3.name =
test.name
LEFT JOIN `test` AS `a4` ON a4.date = 4 AND a4.name =
test.name

It did the job. But would need altering when new data
was added.

Then, I used Zope to make it dynamic, so it
incorporates new weeks' data:

SELECT distinct `test`.`name`,<dtml-in
test_get_date>`week<dtml-var date>`.`level` AS
`week<dtml-var date>`,</dtml-in>
`test`.`name` AS `lose_comma` FROM `test`

<dtml-in test_get_date>
LEFT JOIN `test` AS `week<dtml-var date>`
ON `week<dtml-var date>`.`date` = <dtml-var date>
AND `week<dtml-var date>`.`name` = test.name
</dtml-in>

Please point out any stark errors in my thinking. This
won't be a huge app (but then these things grow I
guess so it's best to get these things right).

--
Matt Johnson

__________________________________________________
Do You Yahoo!?
Everything you'll ever need on one web page
from News and Sport to Email and Music Charts
http://uk.my.yahoo.com