select p.long_title as title,
ps.[name] as STATUS,
i.[name] as industry,
c.[name] as category,
pc.comment as 'comment',
users.first_name + ' ' + users.last_name as commentuser,
pc.last_updated_date as commentdate ,
topicTags.tags
from presentation_comment pc
left join
presentation p
on pc.presentation_id=p.presentation_id
left join
presentation_status ps
on ps.presentation_status_id =p.presentation_status_id
left join industry i on i.industry_id = p.industry_id
left join category c on c.category_id = p.category_id
left join
users on users.user_id=pc.last_updated_user_id
left join
(
SELECT presentation_id,
tags =
(
SELECT distinct ',' + topic.[name] + ','
FROM presentation_topic tmp,
topic
WHERE topic.topic_id = tmp.topic_id AND
presentation_id = presentation_topic.presentation_id FOR
XML PATH ('')
)
FROM presentation_topic
WHERE presentation_id IS NOT NULL
GROUP BY presentation_id
) topicTags
on topicTags.presentation_id = p.presentation_id
left join
(
SELECT presentation_id,
speaker =
(
SELECT ',' + ps. + ','
FROM presentation tmp,
presentation_speaker ps
WHERE topic.topic_id = tmp.topic_id AND
presentation_id = presentation_topic.presentation_id FOR
XML PATH ('')
)
FROM presentation_topic
WHERE presentation_id IS NOT NULL
GROUP BY presentation_id
) topicSpeakers
on topicSpeakers.presentation_id=p.presentation_id
where users.user_id=1
ps.[name] as STATUS,
i.[name] as industry,
c.[name] as category,
pc.comment as 'comment',
users.first_name + ' ' + users.last_name as commentuser,
pc.last_updated_date as commentdate ,
topicTags.tags
from presentation_comment pc
left join
presentation p
on pc.presentation_id=p.presentation_id
left join
presentation_status ps
on ps.presentation_status_id =p.presentation_status_id
left join industry i on i.industry_id = p.industry_id
left join category c on c.category_id = p.category_id
left join
users on users.user_id=pc.last_updated_user_id
left join
(
SELECT presentation_id,
tags =
(
SELECT distinct ',' + topic.[name] + ','
FROM presentation_topic tmp,
topic
WHERE topic.topic_id = tmp.topic_id AND
presentation_id = presentation_topic.presentation_id FOR
XML PATH ('')
)
FROM presentation_topic
WHERE presentation_id IS NOT NULL
GROUP BY presentation_id
) topicTags
on topicTags.presentation_id = p.presentation_id
left join
(
SELECT presentation_id,
speaker =
(
SELECT ',' + ps. + ','
FROM presentation tmp,
presentation_speaker ps
WHERE topic.topic_id = tmp.topic_id AND
presentation_id = presentation_topic.presentation_id FOR
XML PATH ('')
)
FROM presentation_topic
WHERE presentation_id IS NOT NULL
GROUP BY presentation_id
) topicSpeakers
on topicSpeakers.presentation_id=p.presentation_id
where users.user_id=1