I can do what I'm trying to do with a temp table, but I am now trying to do it with a subquery...Here is how I'm doing my query w/ a temp table. The field 'eu.corUser' stores u.userID when a new user external user is created. What I'm doing is populating a temp table w/ u.userID and u.login (username) then joining the temp table to my query so i can get the login of the user that created the new external user. select u.login, u.userIDinto #tempUsersfrom [user] u join corUser cu on cu.userID = u.userIDselect u.login, o.organizationName, s.storeName, t.login from organization o join store s on s.organizationID = o.organizationID join externalUser eu on eu.organizationID = o.organizationID join [user] u on u.userID = eu.userID join #tempUsers t on t.userID = eu.corCreatorwhere corCreator is not nulldrop table #tempUsers
Here is how I'm trying to populate the login (username) based off of corCreator w/ a subquery. I keep getting null for the corName field. select u.login, o.organizationName, s.storeName, (select u.login from [user] u join corUser cu on cu.userID = u.userID join externalUser eu on eu.userID = cu.userID where eu.corCreator = cu.userID ) as corName, eu.corCreator from organization o join store s on s.organizationID = o.organizationID join externalUser eu on eu.organizationID = o.organizationID join [user] u on u.userID = eu.userID
Thanks, sorry if this is confusing. Let me know if there's anything I can do to clear any confusion up.