SET STATISTICS TIME ON;

DECLARE @EventID BIGINT = 229;

SELECT
DISTINCT

CO.CompanyID AS [IDCompany],
US.UserID AS [VisitorID],
CO.CompanyTypeID AS [CompanyTypeID],
CO.Name AS [CompanyName],
ISNULL(CO.[Description],'') AS [Description],
ISNULL(CS.ProductDescription,'') AS [ProductDescription],
ISNULL(CS.TagLine,'') AS [TagLine],
ISNULL(CO.Logo,'') AS [Logo],
ISNULL(CO.CompanyUrl,'') AS [CompanyUrl],
US.FirstName AS [FirstName],
US.LastName AS [LastName],
ISNULL(US.PersonalBiography,'') AS [PersonalBiography],
'' AS Title,
ISNULL(US.JobTitle,'') AS [Position],
NULL AS [SponsorAttendeeNo],
[IsSpekerData].EventMeetingAgendaID

FROM Users.[User] US
INNER JOIN Users.Company CO ON US.CompanyID = CO.CompanyID AND CO.EventID = @EventID
LEFT JOIN Users.CompanySponsors CS ON CO.CompanyID = CS.CompanyID AND CO.EventID = CS.EventID

OUTER APPLY(
SELECT TOP 1 EMA.EventMeetingAgendaID FROM
Events.EventMeetingAgenda EMA
INNER JOIN Events.EventMeetingAgendaParticipant EMAP ON EMA.EventMeetingAgendaID = EMAP.EventMeetingAgendaID
WHERE EMA.EventID = @EventID AND EMA.ActivityTypeID NOT IN (8,9,10) AND EMA.IsDeleted = 0 AND EMAP.IsSpeaker = 1 AND EMAP.UserID = US.UserID) AS [IsSpekerData]

WHERE
US.EventID = @EventID
AND US.IsDeleted = 0
AND US.IsBlocked = 0
AND US.HideOnPanel = 0
AND CO.IsDeleted = 0
AND IsSpekerData.EventMeetingAgendaID IS NOT NULL

ORDER BY [CompanyName] ASC