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

avatar image

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

avatar image

Kev Riley ♦♦
66.8k 48 65 81

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.

Follow this question

By Email:

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



Answers and Comments

SQL Server Central

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



asked: Jan 17, 2011 at 10:23 AM

Seen: 1087 times

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

Copyright 2018 Redgate Software. Privacy Policy