Re: Help with SELECT statement
Date: Mon, 21 Apr 2003 23:22:19 -0700
Message-ID: <3EA4DF9B.CF67BED1_at_exxesolutions.com>
Paul Murphy wrote:
> "Tuan Nguyen" <tuan_at_cao-lanh.com> wrote in message
> news:4hf9avgedtm1fhs97jha1amistpe8j4els_at_4ax.com...
> > Hi all:
> >
> > I have the following table called STUDENTSCORES
> > that looks something like this:
> >
> > ================================================
> > | LastName | FirstName | Score | MakeupScore|
> > | Summer | Kathy | 90 | |
> > | Abad | Joe | | 95 |
> > | Wolf | Eric | 74 | |
> > | Sloan | Jim | | 65 |
> > ================================================
> >
> > how would i be able to select from it so that it
> > would display all the students' names and the
> > scores they have from the approriate column? Like
> >
> > Kathy Summer 90
> > Joe Abad 95
> > Eric Wolf 74
> > Jim Sloan 65
> >
> > Thanks so much for any help!
> >
> > Regards,
> > Tuan
>
> I assume none of the students would ask for homework answers and also none
> of them will have both a score and makeup score.There is more than one way
> to do it, but I'd probably try:
>
> SELECT firstname, lastname, score || makeupscore as grade FROM
> studentscores;
>
> or
>
> SELECT firstname, lastname, score FROM studentscores WHERE score IS NOT NULL
> UNION
> SELECT firstname, lastname, makeupscore FROM studentscores WHERE makeupscore
> IS NOT NULL;
>
> This can probably end up like a Rube Goldberg contest to see who can write
> the most complex SQL query to do the job...
>
> Paul Murphy
Actually my instinct would have been to tell Tuan to do his own homework. But since I'm in a good mood ... just solved an insidious jinit problem ... I'll suggest that those scores are numbers, not strings, so more I'll go for:
SELECT firstname || ' ' || lastname NAME, GREATEST(score, makeupscore) SCORE FROM studentscores;
As I will assume that mistakes take place, sometimes there are values in both columns, and the student should get the larger of the two possible grades.
Oh back to that jinit problem. If you try to install jinit on a Win2K machine with some Hewlett-Packard printer drivers it won't install until you drop the drivers from memory. Go figure. In this case the drivers were the latest set for an HP OfficeJet Model G85.
-- Daniel Morgan http://www.outreach.washington.edu/extinfo/certprog/oad/oad_crs.asp (remove one 'x' from my email address to reply)Received on Tue Apr 22 2003 - 08:22:19 CEST