Selecting Sets of Rows from a Table
-
Hi,
I am trying to get sets of 2 rows from a table in my database. I only want the rows if they both in the table. I have tried to use 2 Exists clauses but it seems that when I do that, I get no results. If I replace the second "AND EXISTS" with "OR EXISTS", I get results but they are not in the pairs that I want.
Basically, from the query below, I want to return the rows that exist in the table where there is a "SurveySetSnapshotID" for each of the "StatFlagID"
SELECT DISTINCT S.SurveySetSnapshotID, S.SurveySetDefinitionID, S.StatFlagID, S.RespondentCode, S.QuestionsCompleted
FROM OpSurveySetSnapshot AS S
WHERE S.RespondentCode IS NOT NULL AND S.QuestionsCompleted =1 AND S.Deleted =0
AND EXISTS
(
SELECT * FROM OpSurveySetSnapshot S1
WHERE S1.StatFlagID = '{233711e2-b972-4fe4-998e-fb197a5ccd8b}' AND S1.Deleted = 0 AND S1.QuestionsCompleted =1
AND S.SurveySetSnapshotID = S1.SurveySetSnapshotID
 
AND EXISTS
(
SELECT * FROM OpSurveySetSnapshot S2
WHERE S2.StatFlagID = '{c4ab69c8-0865-4940-bc63-3d2aae0f2581}' AND S2.Deleted = 0 AND S2.QuestionsCompleted =1
AND S.SurveySetSnapshotID = S2.SurveySetSnapshotID
 
So the results should be like:
Snapshot 1 - Definition 1 - {233711e2-b972-4fe4-998e-fb197a5ccd8b}
Snapshot 2 - Definition 1 - {c4ab69c8-0865-4940-bc63-3d2aae0f2581}
Thanks for the Help,
Matt
-
I may have completely misunderstood your intention. But if what you are looking for is to get only the Respondents who have completed all Surveys (or whatever the StatFlagId represents), then this can be a case for "Relational Division".
Joe Celko has described this in a great article, http://www.developersdex.com/gurus/articles/113.asp.
There is also more in-depth findings in http://www.cs.arizona.edu/people/mccann/research/divpresentation.pdf
Hope this can lead you to a solution.
ola
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply