x

subquery to list all distinct mgrid values

This is the code I have. I am using TSQL Database

select lastname, firstname 
from HR.Employees 
where Employees.mgrid in 
   (select empid 
    from HR.Employees 
    where title = 'managers')
It only returns the column names. What am doing wrong?
more ▼

asked Jan 17, 2011 at 10:23 AM in Default

coljen gravatar image

coljen
21 2 2 2

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

If it is just returning the column headers, then that means it's an empty data set.

Break the query down to see where it is going wrong.

Does the sub-query select empid from HR.Employees where title = 'managers' return any rows?

If it does, is it returning valid values for mgrid that can be fed into the outer query?

Once we have answers for those, then we can talk about making this a JOIN query.
more ▼

answered Jan 17, 2011 at 10:33 AM

Kev Riley gravatar image

Kev Riley ♦♦
53k 47 49 76

may have to use sub query in order to satisfy homework question. May be simply a demonstration of how its implemented/used. You may spoil next chapter by moving on to a JOIN too soon. ++the butler did it++
Jan 18, 2011 at 01:43 AM Fatherjack ♦♦
@Fatherjack LOL! Those pesky kids
Jan 18, 2011 at 01:58 AM Kev Riley ♦♦

@Fatherjack - That's a problem I've had recently. I have been taking some SQL Server courses at Stockholm University. I had to take basic courses to be able to take on an optimization course and a Reporting Services course.

If found out during the basic courses that it's pretty hard to unlearn all the things you've learned, just to complete assignments on a basic enough level...
Jan 18, 2011 at 03:36 AM Magnus Ahlkvist
@Magnus - that must be quite difficult. On the other hand, I'm sure it isn't nice for the professor if you know more than them
Jan 18, 2011 at 04:05 AM WilliamD
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1840
x986

asked: Jan 17, 2011 at 10:23 AM

Seen: 927 times

Last Updated: Jan 17, 2011 at 12:30 PM