CODEWITHSUNDEEP
sqlrecursion
bbbbbbbbbb
bbbbbbbbbb

Reputation: 319

SQL: Recursive query

I've been trying to figure out how to determine how many employees have been hired after a specific employee named Blake. I want to use a recursive query. I have tried the following query to no avail:

Select E.Ename, E.Hiredate
From EMPLOYEES E, EMPLOYEES B
Where e.Hiredate = b.Hiredate
and e.Hiredate > b.Hiredate
and b.Ename = 'blake';

My thinking was that I join the Employees table to itself via 'e.Hiredate = b.Hiredate', what am I missing?

Upvotes: 0

Views: 244

Answers (3)

RichardTheKiwi
RichardTheKiwi

Reputation: 107806

Your query would have worked if you didn't have both

      e.Hiredate = b.Hiredate
  and e.Hiredate > b.Hiredate

I hope you can see how the 2 values cannot ̲*s̲i̲m̲u̲l̲t̲a̲n̲e̲o̲u̲s̲l̲y̲* be equal and unequal!!

Select E.Ename, E.Hiredate
From EMPLOYEES E, EMPLOYEES B
Where e.Hiredate > b.Hiredate
and b.Ename = 'blake';

Of course, since you are asking a fairly basic 101 question, I assume only ONE employee can be named 'blake' (i.e. Ename is a unique identifier), otherwise there are other logical holes to pick.

Since you're starting in SQL, I would also advise you to use ANSI joins instead of the table-list (comma) form, which would look like this:

Select E.Ename, E.Hiredate
  From EMPLOYEES E
  JOIN EMPLOYEES B on e.Hiredate > b.Hiredate
 WHERE b.Ename = 'blake';

Upvotes: 1

user330315
user330315

Reputation: 

select e.*
from employees e
where hiredate > (select max(hiredate)
                  from employees
                  where ename = 'blake');

The max() in the inner query is necessary to ensure that only a single row is returned, even if there are more employees with that name

Upvotes: 0

Malcolm O'Hare
Malcolm O'Hare

Reputation: 5009

Quick and dirty:

SELECT Ename, Hiredate
FROM employees
WHERE hiredate > (SELECT TOP 1 hiredate FROM employees WHERE ename = 'blake')

Upvotes: 0

Related Questions