June 21, 2015 at 4:32 am
Hi everyone, Have been ask this question from the query test of NorthWind database which really confused me
Q:Return the empolyeeID,FirstName + LastName as Fullname for all empoylees that have not made sale since july 1998(Order.OrderDate) (Right join + Sub queries)
*****imagine in a situation that you need to write answer down on paper without querying the database first, how would you solve it?
My solution :
SELECT E.EmployeeID,E.FirstName+ ' '+ E.LastName AS FullName
FROM Orders AS O
Right join Employees AS E
ON E.EmployeeID = O.EmployeeID
WHERE NOT EXISTS
(SELECT O.EmployeeID
FROM Orders O
WHERE O.OrderDate BETWEEN '1998-01-01' AND GETDATE()) 0 row?
The result looks susbicoius cause i had also ran these two queries
SELECT DISTINCT COUNT(*)
FROM Orders AS O
Right join Employees AS E
ON E.EmployeeID = O.EmployeeID Total =830 Row
SELECT DISTINCT O.EmployeeID
FROM Orders O
WHERE O.OrderDate BETWEEN '1998-01-01' AND GETDATE() Total = 9 Row
shoudn't the result be 830-9 =821 Rows?
Hope someone can help out, thanks in advance.
June 21, 2015 at 5:54 am
see if this helps.......as I read your homework, the question relates to employees not number of orders
SELECT
O.EmployeeID
, E.FirstName + ' ' + E.LastName AS Fullanme
FROM Orders AS O
INNER JOIN Employees AS E ON O.EmployeeID = E.EmployeeID
GROUP BY
O.EmployeeID
, E.FirstName
, E.LastName
HAVING (MAX(O.OrderDate) < '1998-08-01');
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
June 21, 2015 at 1:40 pm
Hi SS,
Thanks for your answer, but the question ask specifically to use right join and sub query to solve the problem:-)
June 21, 2015 at 1:46 pm
Eric_Shao (6/21/2015)
Hi SS,Thanks for your answer, but the question ask specifically to use right join and sub query to solve the problem:-)
well....I think the answer is 9 employees and you seem to suggest the answer is 821...care to explain...cos I dont follow you
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
June 21, 2015 at 2:21 pm
Hi SS, Sorry for the confusion, what I really struggle was why my solution didn't work, I though at least return something instead of 0. so it would be much appreciated if you can correct my answer.:-)
Cheers
June 21, 2015 at 2:54 pm
does this make any sense to you........
SELECT distinct O.EmployeeID
FROM Orders O
WHERE O.OrderDate BETWEEN '1998-01-01' AND GETDATE()
SELECT distinct O.EmployeeID
FROM Orders O
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
June 21, 2015 at 3:11 pm
Yep, this make sense, The first one is 9, The second is 830, But what I really want to figure out is how to go about and display this 9 records with associate person's name using right join combined with sub query.
June 22, 2015 at 6:03 am
In your initial post you said the question was to return the EmployeeID, FirstName + LastName as Fullname for all employees that have not made a sale since July 1998 but there are no orders in the Orders table with OrderDate >= '1998-08-01' as you can see from the following:
SELECT MIN(OrderDate) AS [MinOrderDate], MAX(OrderDate) AS [MaxOrderDate] FROM Orders
The wording of the question in your original post would seem to indicate employees who have not made sales later than July 31, 1998 (August 1, 1998 and onward)? Please double-check whether this is what the question is asking, because as you can see there are no orders past May 6, 1998, assuming you are using the same version of the Northwind database that I am.
June 23, 2015 at 5:50 am
Hi Brain, Yes, i'd checked that we on the same page in terms of database, i suppose what the question really asking is any order placed before 06-05-1998. return those empolyee 's name .
June 23, 2015 at 7:47 am
If you are only interested in the employees' names then the query from J Livinston SQL is correct. You can amend it to include the MaxOrderDate if you wish. For example,
SELECT
O.EmployeeID
, E.FirstName + ' ' + E.LastName AS Fullname
, MAX(O.OrderDate) AS MaxOrderDate
FROM Orders AS O
INNER JOIN Employees AS E ON O.EmployeeID = E.EmployeeID
GROUP BY
O.EmployeeID
, E.FirstName
, E.LastName
HAVING (MAX(O.OrderDate) < '1998-08-01');
Are you also supposed to show all the orders associated with these employees?
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply