Query Challange

Im having some problems with a query Im working on and Ive googled it up and down and Im missing something. I have a subquery and I want the results, which return multiple columns, joined to the current tables. I'll try and explain a little better. One of my fields is called 'Assignee_type' and another is 'Assignee_ID'. Now, depending on the 'Assignee_type' depends on the table it looks up the 'Assignee_ID' in. Which is cool, a simple case statement inside the select. If the Assignee_type = 1 then it looks up the values in the Agent table, if its 2 then it looks in the agency table. Here is the cleaned up, help edition so its easier and cleaner to read:

DECLARE @.IDINTSET @.ID = 8--TYPE-- AGENT = 1-- AGENCY = 2SELECT A.ID,CASEWHEN A.ASSIGNEE_TYPE = 1THEN (SELECT AG.AGENT_ID ID, AG.FIRST_NAME +' ' + AG.LAST_NAME [NAME]FROM AGENT AG (NOLOCK)WHERE AG.AGENT_ID = A.ASSIGNEE_ID)WHEN A.ASSIGNEE_TYPE = 2THEN (SELECT AGY.ID, AGY.[NAME]FROM AGENCY AGY(NOLOCK)WHERE AGY.ID = A.ASSIGNEE_ID)END ASSIGNEEFROM APPOINTMENT A (NOLOCK)WHERE A.ID = @.id

Now if I take a column out of each subquery in case statement, so that it returns only 1, it works fine, but I want possibly up to 10 more fields. I tried Temporary Tables, CROSS APPLY and I cant seem to get it.Any help or suggestions would be great. TIA.

I don't know if it was ok to do it as a stored procedure, or if it has to be regular sql... but anyway, here is a stored procedure that does. If you need it as plain sql let me know, and I'll see if I can help

CREATEPROCEDURE sp_appointment

@.IDINT

AS

BEGIN

SETNOCOUNTON;

declare @.TypeINT

declare @.AssigneeIdINT

select @.Type= Assignee_Type, @.AssigneeId= Assignee_Idfrom appointmentwhere id= @.id

if @.Type= 1

begin

SELECT @.ID, AG.AGENT_ID ID,AG.FIRST_NAME+' '+ AG.LAST_NAME [NAME]

FROM AGENT AG(NOLOCK)

WHERE AG.AGENT_ID= @.AssigneeId

end

if @.Type= 2

begin

SELECT @.ID, AGY.ID,AGY.[NAME]

FROM AGENCY AGY(NOLOCK)

WHERE AGY.ID= @.AssigneeIdend

END

GO

|||

SELECT A.ID,A.ASSIGNEE_ID,NAME

FROM Appointment a

LEFT JOIN (

SELECT 1 AS [Type],AGENT_ID AS ID,FIRST_NAME + ' ' + LAST_NAME AS [NAME]

FROM Agent

UNION ALL

SELECT 2 AS [Type],ID,NAME

FROM Agency

) t1

ON t1.ID=A.ASSIGNEE_ID AND t1.[Type]=A.ASSIGNEE_TYPE

WHEREA.ID=@.ID

|||

Try selecting the main table into a temporary table and then run a series of updates, one for each type. When all updates are done, select from the temporary table, to get your output.

(By temporary table, I am referring to an in-memory table variable.)

|||

DECLARE @.IDINTDECLARE @.A_TYPEINTDECLARE @.ttable( idintnot null,name varchar(50)null)SET @.ID = 8SELECT @.A_TYPE = A.IDFROM APPOINTMENT A (NOLOCK)WHERE A.ID = @.idif(@.A_TYPE = 1)begin insert into @.tselect AG.AGENT_ID ID,AG.FIRST_NAME +' ' + AG.LAST_NAME [NAME]from AGENT AG (NOLOCK)where (AG.AGENT_ID = A.ASSIGNEE_ID)end else if(@.A_TYPE = 2)begin insert into @.tselect AGY.ID,AGY.[NAME]from AGENCY AGY(NOLOCK)where AGY.ID = A.ASSIGNEE_ID)endselect *from @.t

|||

Thank you all for your responses. It helped me overcome this challenge and get the results I was looking for and needed.

Klaus - I originally did it this way, but I noticed I would have to repeat alot of code and I knew there had to be a simpler solution, non-the-less it works.

Motley - THANKS. I liked this solution and integrated it into my sproc. UNION ALL was perfect but I didnt think of it :)

Kyle - I didnt get a chance to try your solution, but thats one thing I tried and I couldnt get it to work (table variable), but your solution looks great.

Thanks all again.

But here is the final solution for others to stumble across who is looking:

ALTER PROCEDURE dbo.SEARCH_APPOINTMENT_DETAILS_BYID ( @.idint )ASSELECT'Code: ' +CASE A.CODEWHEN''THEN'N/A'ELSE A.CODEEND CODE,CONVERT(VARCHAR(10), A.EFFECTIVE_DT, 101) EFFECTIVE_DT,ISNULL(CONVERT(VARCHAR(10), A.END_DATE, 101),'PERPETUAL') END_DATE, A.NOTES,ISNULL(AFF.[NAME],' No Affiliate') AFFILIATE_NAME, CR.[NAME] CARRIER_NAME, C.CODE CONTRACT_CODE, t1.ID ASSIGNEE_ID, t1.[NAME] ASSIGNEE_NAME, t1.ADDRESS1, t1.ADDRESS3, t1., t1.PHONEFROM APPOINTMENT A (NOLOCK)LEFTOUTER JOIN [CONTRACT] C (NOLOCK)ON C.ID = A.CONTRACT_IDLEFTOUTER JOIN CARRIER CR (NOLOCK)ON CR.ID = C.CARRIER_IDLEFTOUTER JOIN CARRIER AFF (NOLOCK)ON AFF.ID = A.APP_AFFILIATE_IDLEFTOUTER JOIN (SELECT 1AS [TYPE], AGENT_ID ID, FIRST_NAME +' ' + LAST_NAME [NAME], ADDRESS1, CITY +' ' + STATE +' ' + POSTAL_CODE ADDRESS3,ISNULL(EMAIL_ADDRESS,'N/A') EMAIL, ISNULL(DBO.FUNC_FORMATPHONENUMBER(CONTACT1_VALUE),'') PHONEFROM AGENT (NOLOCK)WHERE DELETED <> 1UNIONALLSELECT 2AS [TYPE], A.ID, A.[NAME], A.ADDRESS ADDRESS1, A.CITY +' ' + S.ABBR +' ' + A.ZIP ADDRESS3,'N/A' EMAIL,ISNULL(DBO.FUNC_FORMATPHONENUMBER(A.PHONE),'') PHONEFROM AGENCY ALEFTOUTER JOIN STATE SON S.ID = A.STATE_ID )t1ON t1.ID=A.ASSIGNEE_IDAND t1.[TYPE]=A.ASSIGNEE_TYPEWHERE A.DELETED <> 1AND A.ID = @.id