Post reply

Selecting Sets of Rows from a Table

  • January 12, 2005 at 8:09 pm

    #61219

    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

    &nbsp

    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

    &nbsp

    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

     

  • January 14, 2005 at 1:32 am

    #537423

    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