Very common requirement to show all members of a campaign and the target products of a campaign in Report. Below query returns the same.
-- Get Members of a Campaign
SELECT DISTINCT EntityId,
CASE
WHEN LM.entitytype=1 THEN 'ACCOUNT'
WHEN LM.entitytype=2 THEN 'CONTACT'
WHEN LM.entitytype=3 THEN 'Lead' END AS 'CUSTOMER TYPE',
CASE
WHEN LM.entitytype=1 THEN ACCOUNT.Name
WHEN LM.entitytype=2 THEN CONTACT.FullName
WHEN LM.entitytype=3 THEN Lead.FullName END AS 'CUSTOMER NAME'
FROM FilteredListMember LM
LEFT JOIN Account ON ACCOUNT.AccountId=EntityId
LEFT JOIN CONTACT ON CONTACT.ContactId=EntityId
LEFT JOIN Lead ON Lead.LeadId=EntityId
WHERE ListId IN
(Select entityid From FilteredCampaignItem where campaignid = 'B5FCD75A-A1AD-E211-854C-000C29749A28' and entitytype=4300)
-- Get Target Products of a campaign.
SELECT DISTINCT entityid, Campaign = (SELECT Name FROM Campaign where campaignid = 'B5FCD75A-A1AD-E211-854C-000C29749A28'),
P.ProductNumber, P.name as productname
FROM
FilteredCampaignItem CMP
JOIN Product P ON P.ProductId=CMP.entityid
where campaignid = 'B5FCD75A-A1AD-E211-854C-000C29749A28' and entitytype=1024
