Select ename, job, sal
from emp
where deptno = (select deptno from dept where loc = 'NEW YORK');
here along with ename, job, sal i want to display even loc column on output screen. how is it possible? I don't want to use joins.
asked
sub queries problem
+1 to Kev's comment - you wouldn't say 'I'd like to drive my car, but I'd rather not have to use the steering wheel'.
You should watch out for when you have two depts with loc = 'NEW YORK'. Then this query will fail. Consider `select MAX(deptno)` instead?
How are you specifying 'NEW YORK' is it a parameter? Then use the parameter:
Select ename, job, sal, @locparameter as loc
from emp
where deptno = (select deptno from dept where loc = @locparameter);
or if it's hardcoded, use the literal:
Select ename, job, sal, 'NEW YORK' as loc
from emp
where deptno = (select deptno from dept where loc = 'NEW YORK');
or even a crazy way :
select ename, job, sal,
(select loc from dept where deptno = emp.deptno) as loc
from emp
where deptno = (select deptno from dept where loc = 'NEW YORK');
To answer your comment....
you would get the dname using another subquery, like
(select dname from dept where deptno = emp.deptno) as dname
You really should use joins for this. They will perform much better than sub queries, and handle the cases where you may have multiple depts for a loc or a emp, like Rob has mentioned in the comment to your question.
