Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 I need to figure out why there is no output...

Author  Topic 

marcelkouatly
Starting Member

2 Posts

Posted - 2013-06-13 : 22:09:32
The code runs fine and I do not seem to figure out where I am making the mistake. I am a student and would like explanations rather than an answer, please show me what I am doing wrong. Thank you.

The whole purpose here is to select the employeid, lastname, firstname
for the employee with sales but whom does not have a manager.
I need an inner join and a subquery on the code.

USE TSQLFUNDAMENTALS2008;
SELECT lastname, firstname
FROM HR.Employees INNER JOIN Sales.Orders
ON HR.Employees.empid = Sales.Orders.empid
WHERE orderid =
(SELECT empid FROM HR.Employees
WHERE mgrid = NULL)
;

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2013-06-13 : 22:32:11
1) WHERE mgrid = NULL needs to be WHERE mgrid IS NULL
2) This will need to be IN not =
Go to Top of Page

marcelkouatly
Starting Member

2 Posts

Posted - 2013-06-13 : 22:51:13
Hi LozInSpace thank you for your insight however I was unable to get a result... I get blank rows but I know for certain that I should be getting one row of output.


USE TSQLFUNDAMENTALS2008;
SELECT lastname, firstname
FROM HR.Employees INNER JOIN Sales.Orders
ON HR.Employees.empid = Sales.Orders.empid
WHERE orderid IN
(SELECT empid FROM HR.Employees
WHERE mgrid IS NULL)
;
Go to Top of Page

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-06-13 : 22:52:33
In addition to what LoztInSpace stated.

Do you want to evaluating whether OrderId is equal to empid ?
That is what you are doing in the red hightlighted text below.

If you want to evaluate mgrid value, it suffices to say
WHERE mgrid is NULL
Since you are joining HR.Employees and Sales.Orders tables, you dont need another select statement in your where clause.
Run the following query to see all the data available to you when you join the above two tables:
[CODE]
SELECT * FROM HR.Employees H INNER JOIN Sales.Orders S
ON H.empid = S.empid;
[/CODE]

quote:
Originally posted by marcelkouatly


USE TSQLFUNDAMENTALS2008;
SELECT lastname, firstname
FROM HR.Employees INNER JOIN Sales.Orders
ON HR.Employees.empid = Sales.Orders.empid
WHERE orderid =
(SELECT empid FROM HR.Employees
WHERE mgrid = NULL)
;

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-14 : 01:30:04
Fromyour explanation this is what you want I guess


USE TSQLFUNDAMENTALS2008;
SELECT lastname, firstname
FROM HR.Employees INNER JOIN Sales.Orders
ON HR.Employees.empid = Sales.Orders.empid
WHERE HR.mgrid IS NULL


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
   

- Advertisement -