subquery question - 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

General SQL Server Forums

New to SQL Server Programming

subquery question

Author

Topic

crugerenator
Posting Yak Master

126 Posts

Posted - 2008-12-26 : 13:14:09

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.userID

into		#tempUsers

from		[user] u
			join corUser cu
				on cu.userID = u.userID


select		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.corCreator

where		corCreator is not null

drop 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.

revdnrdy
Posting Yak Master

220 Posts

Posted - 2008-12-26 : 15:40:19

This part seems incorrect to me

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

Perhaps you need to use a WHERE EXISTS clause before you invoke the imbedded select u.login... clause ?

something like

Select u.login, o.organization ..
from sometable u sometable o
where exists (select .... join...)

something like that?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-27 : 11:53:22

[code]
select u.login,
o.organizationName,
s.storeName,
tmp.login 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
join
(select u.login,
u.userID
from [user] u
join corUser cu
on cu.userID = u.userID
join externalUser eu
on eu.userID = cu.userID
)tmp
on eu.corCreator = tmp.userID
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources