Conditional data Selction

[c100cbk2]

I ned to be able to selcet data on the following

if a user has sub_agents attached to the I need to pull back only records where there is a hit on the subagent table however if the User has no agents attached then I ned to be able to select all records that are relvent to the user;
Normally I would use either either sub query or a join but the requirement that all returned if no agents are attached is stumping me,

Can anybody help?

 

[gmmastros]

What have you tried so far? Sample data (even if made up) would be helpful here.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[markros]

There was very similar thread yesterday on MSDN and I believe for this particular problem the only solution is to create 2 separate queries and do it using IF statements. You can also do

select U.* into #TempResult 
from Users U inner join Sub_Agents SA on U.UserID = SA.UserID

IF @@ROWCOUNT = 0
  select * from Users
else
  select * from #TempResult

PluralSight Learning Library