give me a code, please. - SQL Server Forums

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

SQL Server 2000 Forums

Transact-SQL (2000)

give me a code, please.

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-09-20 : 08:32:27

AA writes "I have three tables, one is called employee, which have emp_id and emp_name, the second table is called projects, which have proj_id, proj_name, proj_dur (the time that takes a project to be completed, its an int), and the third table is a lock table called emp_proj with emp_id and proj_id which links both of master tables, now i want to retrieve data from all tables,
**emp_id, emp_name, proj_name, proj_dur**
the data is getting back all the projects that an employee is in, for all employees, and if an employee have more than one project, retrieve only the project with the max duration, i know this might be easy, but i was wondering which to use, cursors or only a static select statement.
thank you."

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-20 : 08:58:34

use a select statament. there is really no need for cursor.

Go with the flow & have fun! Else fight the flow

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-20 : 10:26:42

i'm in a good mood today so this should help


select	t1.*
from
(
	select 	ep.emp_id, e.emp_name, p.proj_name, p.proj_dur
	from 	@emp_proj ep 
		inner join @Employee e on (ep.emp_id = e.emp_id)
		inner join @Projects p on (ep.proj_id = p.proj_id)
) t1
inner join 
(
	select 	ep.emp_id, max(p.proj_dur) as proj_dur
	from 	@emp_proj ep 	
		inner join @Projects p on (ep.proj_id = p.proj_id)
	group by ep.emp_id
) t2 on t1.emp_id = t2.emp_id and t1.proj_dur = t2.proj_dur
order by t1.emp_id

Go with the flow & have fun! Else fight the flow

kselvia
Aged Yak Warrior

526 Posts

Posted - 2004-09-20 : 12:41:45

There you go with that ESP again spirit.

--Ken
I want to die in my sleep like my grandfather, not screaming in terror like his passengers.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-21 : 04:36:14

Go with the flow & have fun! Else fight the flow

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources