• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

scope of alias in subquery

 
Jane Somerfield
Ranch Hand
Posts: 193
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 381
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 193
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 35279
384
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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:


 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic