question

Nani avatar image
Nani asked

sub queries problem

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.

t-sqlsql-server-2000sub-query
6 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Why not use joins?
1 Like 1 ·
+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'.
0 Likes 0 ·
+1 to Kev. Why on earth do you not want to use joins?
0 Likes 0 ·
i do use joins, but i am checking weather every join can be replaced with subquery and vice-versa. Is it possible?
0 Likes 0 ·
You should watch out for when you have two depts with loc = 'NEW YORK'. Then this query will fail. Consider `select MAX(deptno)` instead?
0 Likes 0 ·
Show more comments
Kev Riley avatar image
Kev Riley answered

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.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Steinar avatar image
Steinar answered

change it to

Select ename, job, sal, loc
from emp 
where deptno = (select deptno from dept where loc = 'NEW YORK');
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

no it wont work steiner, i already tried that.
0 Likes 0 ·
sorry, read the question wrong :-( Thought loc was a column in emp..
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.