Lookup Tables And Joins

In a table I'm storing as integers recCreatedBy and recModifiedBy values; I have a lookup table I'm doing an INNER JOIN with to pull a string value for an Alias 'CreatedBy'. I'm trying to Alias a 'ModifiedBy' from the same lookup table in the query. Any help would be greatly appreciated.
SELECT tblStaffPhysicians.docIndex, tblStaffPhysicians.docFullName, tblUsers.userLogin AS CreatedBy
FROM tblStaffPhysicians INNER JOIN
tblUsers ON tblStaffPhysicians.recCreatedBy = tblUsers.userIndexIf I understand you correctly, the SQL would look like this:

SELECT tblStaffPhysicians.docIndex, tblStaffPhysicians.docFullName, Created.userLogin AS CreatedBy, Modified.userLogin AS ModifiedBy
FROM tblStaffPhysicians INNER JOIN
tblUsers Created ON tblStaffPhysicians.recCreatedBy = Created.userIndex
LEFT OUTER JOIN tblUsers Modified ON tblStaffPhysicians.recModifiedBy = Modified.userIndex

I am assuming recModifiedBy could be NULL. If that is incorrect, you could just do an inner join.

Hope that helps!!

BobP|||Thank you so much for your help; it looks like your suggestion is the solution. Thanks again.

Lookup bug (altering caching breaks joins)

There is a bug in the Lookup components, that changing the caching mode breaks the join logic.This is even documented!
AFAICT, if you want the full join logic (where it matches on null values as well), you simply must use full caching everywhere, and install more memory as needed. :(
Does anyone know if this will be fixed with the next version, or in the release?

This is not a bug, as it is by design. If you would like to see a change in functionality please file a design change request in betaplace (or let me know if you can not and I will file one on your behalf.

Thanks,|||"Enabling memory caching breaks joins" seems to me like an annoying bug that is likely to bite newbies, even if they call it a "feature".
I cannot get into betaplace (I went around in circles with it for a while, trying to click on invisible buttons, and posting to the Microsoft SSIS beta newsgroups asking for help, and I never got any success).
If you could post it as a bug (or, um, "feature" that is likely to cause grief to users), that would be great.