Multiple Columns Map to Different Rows in Join Table

Can a SQL statement be written that returns all of the information in
the Project table and the corresponding FullName for both the
CreatedBy and LastModifiedBy fields? Table structures are below.
A typical JOIN clause seems insufficient because every row in the
Project table maps to 2 rows in the User table. When doing reporting,
I've always avoided this problem by using subreports to do individual
lookups. However, I'm curious to see if there is a more direct
approach using SQL only.
Project
- ProjectNo
- ProjectName
- CreatedByUserNo
- LastModifiedByUserNo
User
- UserNo
- FullName
Your input is greatly appreciated.
Best Regards,
DavidSELECT P.projectno, P.projectname,
C.fullname AS createdbyuser,
M.fullname AS lastmodifiedbyuser
FROM Project AS P
JOIN User AS C
ON P.createdbyuserno = C.userno
LEFT JOIN User AS M
ON P.lastmodifiedbyuserno = M.userno
(untested)
--
David Portas
--
Please reply only to the newsgroup
--