May 16, 2008 at 12:08 am
I'm not sure if I'm posting this in the correct place but I'm taking a course about SQL. My current assignment involves creating a query that pulls a list of employees and their supervisor from the Northwind database. The query has to include a subquery. I have been able to develop a query that pulls all of the employees except the CEO, which is Andrew Fuller. Anyone have any ideas as to what I'm missing?
SELECT Employees.LastName, Employees.FirstName, [Supervisors].FirstName AS ManagerFirstName,
[Supervisors].LastName AS ManagerLastName
FROM Employees, Employees AS Supervisors
WHERE Employees.ReportsTo IN
(SELECT ReportsTo FROM Employees WHERE ReportsTo = [Supervisors].EmployeeId);
May 16, 2008 at 12:47 am
Hi;
This is a self join example.
SELECT e2.*--e2.firstname + ' ' + e2.lastname
FROM [Employees] e1 INNER JOIN [Employees] e2
ON e1.employeeID = e2.reportsto
May 16, 2008 at 5:44 am
[font="Verdana"]Also make a habit of avoiding subqueries. Instead apply Joins like suggested by Mr. Hasan in his prev post.
Mahesh[/font]
MH-09-AM-8694
May 16, 2008 at 6:19 am
OP says the assignment requires a subquery, in which case this works:
SELECT e.LastName AS EmpLastName, e.FirstName AS EmpFirstName, s.LastName AS SupLastName, s.Firstname AS SupFirstName
FROM Employees e JOIN Employees s ON e.EmployeeID = s.EmployeeID
WHERE s.ReportsTo IN
(SELECT e.ReportsTo) OR s.ReportsTo IS NULL
Cath
May 16, 2008 at 6:59 am
Yikes. If they're teaching you to use the IN clause with a sub-select, time to run. Get them to hire Jeff Moden or one of other people from here to teach.
Sorry, but that's effectively a cursor. Using a join or a join with a derived table (which could be classified as a subselect) would be better. Something like this:
SELECT a.Col
,b.Col
FROM dbo.Table1 AS a
JOIN (SELECT b.Col
,b.Id
FROM dbo.Table2 b) AS b
ON a.Id = b.Id
WHERE....
Although you would usually have other criteria in the derived table because, as written, this could just be a join against Table2.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 16, 2008 at 11:40 am
Thanks Grant and others. We basically did the same query as a JOIN last week. This week we are covering subqueries and I agree the JOIN seems to be a much better option. It is crisper looking and easier to form. I had no trouble doing the same thing last week but this week it took me an hour to get the employees minus the CEO.
Definitely appreciate the help.
May 16, 2008 at 11:49 am
Not going into the code, this is actually a good time for you to also try out writing a recursive CTE (since you are using SQL Server 2005, based on where you posted this question).
Do a little reading in BOL, it will help you out. I haven't had a reason to write any as of yet myself.
😎
May 16, 2008 at 8:39 pm
Heh... Grant will probably agree... I wouldn't teach recursion if they doubled my salary! I've got ethics! 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
May 18, 2008 at 6:59 pm
Yeah, I've got them ethics things too. That and there seem to be laws against using sticks on the students. When did that happen?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply