Divide operation in SQL

I have a database in which a table employee exist as follow

+-----------+--------------+-------------+
| LastName  | DepartmentID | projectname |
+-----------+--------------+-------------+
| Rafferty  |           31 |  ims        |
| Jones     |           33 |  ibm server |
| Steinberg |           33 |  null       |
| Robinson  |           34 |  ims        |
| Smith     |           34 |  ibmserver  |
| John      |         NULL |  ims        |
+-----------+--------------+-------------+

i want to retrieve those last name who work on a project on which jones working

Select * from employee ???

any suggestion please

Answer

Following should be your query –

Select * from employee where projectname = (select projectname from employee where LastName = 'Jones');

We have not used in clause as Jones is working in one project.

If he is working in multiple projects

then query can be –

Select * from employee where projectname in (select projectname from employee where LastName = 'Jones');

Thanks