Hi,

I have a store procedure and its below

ALTER PROC [dbo].[Event_Report] 

AS
BEGIN

(select p.NominatorOrganisation as Event_Att_Name from POAForm p
union
select i.Name from InKindSponsorshipForm i
union
select n.BeneficiarySponsorName from NominationDetailsForm n)
(select p.NominatorDesignation as Event_Att_Designation from POAForm p
union
select i.Designation from InKindSponsorshipForm i
union
select n.Designation from NominationDetailsForm n)
(select p.ContactNoOne as Event_Att_OfficeTel from POAForm p
union
select i.ContactNoOne from InKindSponsorshipForm i
union
select n.ContactNoOne from NominationDetailsForm n)
(select p.ContactNoTwo as Event_Att_Mobile from POAForm p
union
select i.ContactNoTwo from InKindSponsorshipForm i
union
select n.ContactNoTwo from NominationDetailsForm n)
(select p.NominatorEmail as Event_Att_Email from POAForm p
union
select i.Email from InKindSponsorshipForm i
union
select n.Email from NominationDetailsForm n)

select e.EventName,e.VenueName,e.EventTime,e.GuestOfHonoursName,
e.GuestOfHonoursDesignation, e.EventBrochureOrInvitation,e.LocationMap,
e.Capacity,e.URLMapLink, tp.ThirdPartySaluation,tp.ThirdPartyName, tp.ThirdPartyOrganization, 
tp.ThirdPartyDesignation, tp.ThirdPartyOfficeTel,tp.ThirdPartyMobileNo,tp.ThirdPartyEmail
from Event e INNER JOIN Link l on e.EventID = l.EventID join ThirdPartyEventInvitation tp
on l.ThirdPartyEventInvID = tp.ThirdPartyEventInvID join POAForm p on tp.ThirdPartyEventInvID =
p.ThirdPartyEventInvID join InKindSponsorshipForm i on p.POAFormID = i.POAFormID join 
NominationDetailsForm n on p.POAFormID = n.POAFormID 
where e.DeleteStatus = 'N' and p.DeleteStatus = 'N' and i.DeleteStatus = 'N' and n.DeleteStatus='N'
and tp.DeleteStatus='N'

END

The problem is how to combine between these select set because i cant use union or union all.

eg. I would like to combine

(select p.NominatorOrganisation as Event_Att_Name from POAForm p
union
select i.Name from InKindSponsorshipForm i
union
select n.BeneficiarySponsorName from NominationDetailsForm n)

????.......what word shoul i use between them to connect???????

(select p.NominatorDesignation as Event_Att_Designation from POAForm p
union
select i.Designation from InKindSponsorshipForm i
union
select n.Designation from NominationDetailsForm n)

????.......what word shoul i use between them to connect???????

select e.EventName,e.VenueName,e.EventTime,e.GuestOfHonoursName,
e.GuestOfHonoursDesignation, e.EventBrochureOrInvitation,e.LocationMap,
e.Capacity,e.URLMapLink, tp.ThirdPartySaluation,tp.ThirdPartyName, tp.ThirdPartyOrganization, 
tp.ThirdPartyDesignation, tp.ThirdPartyOfficeTel,tp.ThirdPartyMobileNo,tp.ThirdPartyEmail
from Event e INNER JOIN Link l on e.EventID = l.EventID join ThirdPartyEventInvitation tp
on l.ThirdPartyEventInvID = tp.ThirdPartyEventInvID join POAForm p on tp.ThirdPartyEventInvID =
p.ThirdPartyEventInvID join InKindSponsorshipForm i on p.POAFormID = i.POAFormID join 
NominationDetailsForm n on p.POAFormID = n.POAFormID 
where e.DeleteStatus = 'N' and p.DeleteStatus = 'N' and i.DeleteStatus = 'N' and n.DeleteStatus='N'
and tp.DeleteStatus='N'

Please, can anyone give me idea for this porblem. Thanks in advance.