Using QuerySpec I'm trying to construct the following sql:
SELECT .* dbo.ReportFiles rf
INNER JOIN dbo.CampaignReports cr ON rf.CampaignReportId = cr.CampaignReportId
INNER JOIN dbo.CampaignLocations cl ON cr.CampaignLocationId = cl.CampaignLocationId
INNER JOIN dbo.Campaigns ca ON cl.CampaignId = ca.CampaignId
WHERE
ca.CustomerId = @customerId
AND ca.CampaignId in (123, 456, 789)
AND cr.ReportDate BETWEEN @periodFrom AND @periodTo
AND EXISTS (SELECT * FROM dbo.ReportFileTags rft WHERE rft.ReportFileId = rf.ReportFileId AND rft.FileTag = @tag)
I can't figure out how to get the WhereExists right and I can't find any examples.
Here is how far I got in VB:
Dim qf As New QueryFactory()
Dim q = qf.ReportFiles.From( _
QueryTarget _
.InnerJoin(CampaignReportsEntity.Relations.ReportFilesEntityUsingCampaignReportId) _
.InnerJoin(CampaignLocationsEntity.Relations.CampaignReportsEntityUsingCampaignLocationId) _
.InnerJoin(CampaignsEntity.Relations.CampaignLocationsEntityUsingCampaignId)) _
.Where( _
(CampaignsFields.CustomerId = lCustomerId) _
And (CampaignsFields.CampaignId.In(lLstOfCampaignIds)) _
And (CampaignReportsFields.ReportDate.Between(lPeriodFrom, lPeriodTo)))
Can anyone help?