Hi all,
I am struggling trying to convert the following T-SQL into an ICriteria query. I guess to start with I'll need a DetachedCriteria for the correlated subquery part, but I'm scratching my head with a) how to achieve the HAVING clause and b) how to best do the joins and the restrictions on each of the tables (Alias or Criteria).
Any help or example of something remotely similar would be great. Google is not my friend today!
Code:
DECLARE @state varchar(10) = 'Started';
SELECT s.Id as SeriesId, Count(e.Id) AS EpisodeCount
FROM Series s
JOIN SeriesWorkflowState sws ON s.WorkflowState_id = sws.Id
JOIN Brand b on b.Id = s.Brand_id
JOIN BrandWorkflowState bws ON b.WorkflowState_id = bws.Id
JOIN Episode e ON e.Series_id = s.Id
JOIN EpisodeWorkflowState ews ON e.WorkflowState_id = ews.Id
WHERE (sws.Compliance_State = @state)
AND (sws.Metadata_State = @state)
AND (sws.Availability_State = @state)
AND (sws.Images_State = @state)
AND (sws.Video_State = @state)
AND (bws.Metadata_State = @state)
AND (bws.Images_State = @state)
AND (ews.Compliance_State = @state)
AND (ews.Metadata_State = @state)
GROUP BY s.Id
HAVING Count(e.Id) = (SELECT Count(Id) FROM Episode WHERE Episode.Series_id = s.Id)
Thanks,
Anthony
Login
FAQ
Search