correlated subqueries (response)

In article <1993Feb10.060028.28281_at_wlbr.iipo.gtegsc.com> I wrote:

>To reformat the following table "tuition":
>
>yr semester fees
>---- ---- ----
>1985 1 445
>1985 2 456
>1985 3 501
>1986 1 517
>1986 2 589
>1986 3 605
>1987 1 606
>1987 2 613
>1987 3 822
>
>I ran the following query using Transact-SQL:
>
>select yr,
> sem1 = (select fees from tuition
> where semester = 1 and yr = t.yr),
> sem2 = (select fees from tuition
> where semester = 2 and yr = t.yr),
> sem3 = (select fees from tuition
> where semester = 3 and yr = t.yr)
>from tuition t
>
>Which produced the following reformatted output:
>
>yr sem1 sem2 sem3
>---- ---- ---- ----
>1985 445 456 501
>1986 517 589 605
>1987 606 613 822
>
>The question I have is this. Since rows from the outer query are
>passed in, one at a time, to the inner "correlated" subqueries, why
>are there not three rows for 1985, three rows for 1986, and three rows
>for 1987, in the reformatted result?
>

Apparently, other RDBMSs work the way I expected Transact-SQL to work, as the response I received from Tom Warfield <vnunet!twarfield> shows. Tom tried it using VAX RDB:

>
>select yr, (select fees from tuition
> where semester = 1 and yr = t.yr),
> (select fees from tuition
> where semester = 2 and yr = t.yr),
> (select fees from tuition
> where semester = 3 and yr = t.yr)
>from tuition t;
>

and got the following results:

>
> YR
> 1985 445 456 501
> 1985 445 456 501
> 1985 445 456 501
> 1986 517 589 605
> 1986 517 589 605
> 1986 517 589 605
> 1987 606 613 822
> 1987 606 613 822
> 1987 606 613 822
>9 rows selected
>

Thanks Tom. Anyone else want to try?

Glen Sievertson Received on Thu Feb 11 1993 - 08:23:22 CET