Re: Stranger Results of Select Statements

From: Ed Prochak <prochak_at_my-deja.com>
Date: Sat, 30 Oct 1999 05:01:50 GMT
Message-ID: <7vdu3u$usf$1@nnrp1.deja.com>

In article <_OqS3.4897$Tf7.1963_at_nuq-read.news.verio.net>,   "Jingyan" <jingyan.xu_at_tst2000.com> wrote:
> Hi,
> The result of select statement is 6 rows selected.
>
> select name, age, lodging
> from worker w
> where age=(select max(age) from worker
> where w.lodging=lodging);

>
> But, why the result of this a little different select statement is 1
rows
> selected?
>
> select name, age, lodging
> from worker
> where age=(select max(age) from worker w
> where w.lodging=lodging);
>
> Below is table WORKER
> NAME AGE LODGING
> ------------------------- ---------

---

> BART SARJEANT 22 CRANMER
> ELBERT TALBOT 43 WEITBROCHT
> DONALD ROLLO 16 MATTS
> JED HOPKINS 33 MATTS
> WILLIAM SWING 15 CRANMER
> JOHN PEARSON 27 ROSE HILL
> GEORGE OSCAR 41 ROSE HILL
> KAY AND PALMER WALLBOM ROSE HILL
> PAT LAVAY 21 ROSE HILL
> RICHARD KOCH AND BROTHERS WEITBROCHT
> DICK JONES 18 ROSE HILL
> ADAH TALBOT 23 PAPA KING
> ROLAND BRANDT 35 MATTS
> PETER LAWSON 25 CRANMER
> VICTORIA LYNN 32 MULLERS
> WILFRED LOWELL 67
> HELEN BRANDT 15
> GERHARDT KENTGEN 55 PAPA KING
> ANDREW DYE 29 ROSE HILL
>
> 19 rows selected.
>
> Thank you!
> Jingyan
>

Look at your results and you get a hint. The first one returns 6 different lodgings, right. You have to look at it the way ORACLE does. There is a table alias for worker, w, in both queries. So think of it as really looking at two different tables: W and WORKER.

In the first one has W in the main query and WORKER in the subquery. So the subquery looks for records for each lodging in table W.

The second query has a gotcha. When you do not provide the table name for the attribute, it defaults to the table of the closest enclosing query. so its where clause tries to match w.lodging to lodging from table W. In other words there is not connection between the subquery and the main query in this case. The subquery finds the one maximum value for the whole table.

Hope this helps.

You're welcome.

--
Ed Prochak
Magic Interface, Ltd.
ORACLE Development, conversions, training and support 440-498-3700 magic_at_interfacefamily.com

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Sat Oct 30 1999 - 00:01:50 CDT