Getting top 3 from another table?

[Guest_imported]

I want to do a select, then for every record in that select I want to get the "top 3" from another table. Except I want it all to be in a single statement and return a single row per employee.

So, I've got a table called employee which contains records of employee and another table called projects.

Here's the employee table Project table

EmpId EmpName Projd EmpId ProjName
------------------------- -------------------------
1 John 55 1 ABCD
2 Betty 56 2 EFGH
3 Frank 57 1 IJHQ

I want results like this:

EmpId EmpName ProjName1 ProjName2 ProjName3
-------------------------------------------------------
1 John ABCD IJHQ
2 Betty EFGH
3 Frank

Can't figure out how to do it in a single run at the database.

 

[Tim1]

Hiya,

Why don't you try using the same table more than once in a single select query.

e.g.

SELECT emp.EmpId,
emp.EmpName,
pro1.ProjName "ProjName1",
pro2.ProjName "ProjName2",
pro3.ProjName "ProjName3"
FROM employee emp,
project pro1,
project pro2,
project pro3
WHERE emp.EmpId *= pro1.EmpId
AND emp.EmpId *= pro2.EmpId
AND emp.EmpId *= pro3.EmpId
AND pro2.Projd != pro1.Projd
AND pro3.Projd != pro2.Projd
AND pro3.Projd != pro2.Projd

The outer joints (*= syntax) will mean that either a project or a [NULL] will be returned for each of the three projects, while the AND's will make sure that they are three distinct projects.

HTH

Tim