GeeCON Prague 2014*
The moose likes JDBC and the fly likes scope of alias in subquery Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


JavaRanch » Java Forums » Databases » JDBC
Bookmark "scope of alias in subquery" Watch "scope of alias in subquery" New topic
Author

scope of alias in subquery

Jane Somerfield
Ranch Hand

Joined: Jul 20, 2002
Posts: 193
Can I do this?

select e.NAME, SALARY from EMP where SALARY = (select max(SALARY) from EMP e);

Does the scope of the alias "e" in the subquery matter?
Maximilian Xavier Stocker
Ranch Hand

Joined: Sep 20, 2005
Posts: 381
Originally posted by Jane Somerfield:
Can I do this?

select e.NAME, SALARY from EMP where SALARY = (select max(SALARY) from EMP e);

Does the scope of the alias "e" in the subquery matter?


I am confused. Why the need for the alias at all in that query?
Jane Somerfield
Ranch Hand

Joined: Jul 20, 2002
Posts: 193
The example above may not be very good.

Please see a better one below:

Select n.full_name, a.street, a.city, a.zip, a.state From address a
Where a.id = (Select n.id from name n Where n.full_name Like 'John%');

This query is wrong because the alias "n" is out of acope. How should
I use one query to list the full name and address?
Jeanne Boyarsky
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 30580
    
154

Jane,
The reason that SQL won't work is that there isn't necessarily only one row that matches. What if two rows had the same id and a different name containing John?

Instead, you can use a regular join:




[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
 
GeeCON Prague 2014
 
subject: scope of alias in subquery