Check that a specific Group By condition does not exist

  • CREATE TABLE #tblBlocks

    (

    TemplateID int,

    BlockID int,

    FieldID int,

    BaseStage bit

    )

    GO

    INSERT INTO #tblBlocks (TemplateID, BlockID, FieldID, BaseStage)

    SELECT 1, 1, 1, 0 UNION ALL

    SELECT 1, 1, 2, 0 UNION ALL

    SELECT 1, 1, 3, 0 UNION ALL

    SELECT 1, 2, 4, 0 UNION ALL

    SELECT 1, 2, 5, 1 UNION ALL

    SELECT 1, 2, 6, 0

    GO

    --SELECT * FROM #tblBlocks

    DROP TABLE #tblBlocks

    I need to determine if any group of records in the table above - Grouped by BlockID - does not contain a value of 1 (or true) in the BaseStage column.

    So, to the question - does any group of records, grouped by BlockID, not contain the value 1 (or true) in the records in the above temporary table - the answer would be 'Yes, there is a group without a true BaseStage value.

    How can I ascertain this? I've tried all manner of Group Bys and can't figure it out.

    Thanks for any help.

  • SELECT b.*

    FROM #tblBlocks b

    WHERE NOT EXISTS (

    SELECT 1

    FROM #tblBlocks bi

    WHERE bi.BlockID = b.BlockID

    AND bi.BaseStage = 1)

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • Try this:

    CREATE TABLE #tblBlocks

    (

    BlockID int,

    FieldID int,

    BaseStage bit

    )

    GO

    INSERT INTO #tblBlocks (BlockID, FieldID, BaseStage)

    SELECT 1, 1, 0 UNION ALL

    SELECT 1, 2, 0 UNION ALL

    SELECT 1, 3, 0 UNION ALL

    SELECT 2, 4, 0 UNION ALL

    SELECT 2, 5, 1 UNION ALL

    SELECT 2, 6, 0

    GO

    SELECT

    *

    FROM

    #tblBlocks tb1

    WHERE

    NOT EXISTS(SELECT

    1

    FROM

    #tblBlocks tb2

    WHERE

    tb2.BlockID = tb1.BlockID AND

    tb2.BaseStage = 1);

    go

    --SELECT * FROM #tblBlocks

    DROP TABLE #tblBlocks

    go

  • Thanks for your reply ... it's giving me the opposite of what I want because I phrased my question badly.

    I have modified my temporary table in my first post to include a TemplateID

    Join to the temporary table but only if every group within the temporary table has a BaseStage of 1.

    i.e. make it so that if there is a group without a BaseStage of 1 - it returns nothing - no join happens.

  • sku370870 (9/7/2012)


    Thanks for your reply ... it's giving me the opposite of what I want because I phrased my question back to front.

    What I need is:

    Join to the temporary table but only if every group within the temporary table has a BaseStage of 1.

    i.e. make it so that if there is a group without a BaseStage of 1 - it returns nothing - no join happens.

    Not sure what you are asking. You only provided one table in your initial post. Also, if I read this correction correctly, based on the data provided in the initial post, you want nothing back as none of the groups have a BaseStage = 1 for all rows in a group.

  • Apologies, I was modifying my first post when I thought - that will just confuse the issue. Can we start again please?

    CREATE TABLE #tblBlocks

    (

    TemplateID int,

    BlockID int,

    FieldID int,

    BaseStage bit

    )

    GO

    INSERT INTO #tblBlocks (TemplateID, BlockID, FieldID, BaseStage)

    SELECT 1, 1, 1, 0 UNION ALL

    SELECT 1, 1, 2, 0 UNION ALL

    SELECT 1, 1, 3, 0 UNION ALL

    SELECT 1, 2, 4, 0 UNION ALL

    SELECT 1, 2, 5, 1 UNION ALL

    SELECT 1, 2, 6, 0

    GO

    --SELECT * FROM #tblBlocks

    DROP TABLE #tblBlocks

    If I join to the Table above on TemplateID - I need it to return nothing if any BlockID group does not contain a BaseStage of 1.

    Sorry for screwing up.

  • Again, based solely on the initial post with a little more data added:

    CREATE TABLE #tblBlocks

    (

    BlockID int,

    FieldID int,

    BaseStage bit

    )

    GO

    INSERT INTO #tblBlocks (BlockID, FieldID, BaseStage)

    SELECT 1, 1, 0 UNION ALL

    SELECT 1, 2, 0 UNION ALL

    SELECT 1, 3, 0 UNION ALL

    SELECT 2, 4, 0 UNION ALL

    SELECT 2, 5, 1 UNION ALL

    SELECT 2, 6, 0 UNION ALL

    SELECT 3, 7, 1 UNION ALL

    SELECT 3, 8, 1 UNION ALL

    SELECT 3, 9, 1

    GO

    SELECT

    *

    FROM

    #tblBlocks tb1

    WHERE

    NOT EXISTS(SELECT

    1

    FROM

    #tblBlocks tb2

    WHERE

    tb2.BlockID = tb1.BlockID AND

    tb2.BaseStage = 0);

    go

    --SELECT * FROM #tblBlocks

    DROP TABLE #tblBlocks

    go

  • sku370870 (9/7/2012)


    Apologies, I was modifying my first post when I thought - that will just confuse the issue. Can we start again please?

    CREATE TABLE #tblBlocks

    (

    TemplateID int,

    BlockID int,

    FieldID int,

    BaseStage bit

    )

    GO

    INSERT INTO #tblBlocks (TemplateID, BlockID, FieldID, BaseStage)

    SELECT 1, 1, 1, 0 UNION ALL

    SELECT 1, 1, 2, 0 UNION ALL

    SELECT 1, 1, 3, 0 UNION ALL

    SELECT 1, 2, 4, 0 UNION ALL

    SELECT 1, 2, 5, 1 UNION ALL

    SELECT 1, 2, 6, 0

    GO

    --SELECT * FROM #tblBlocks

    DROP TABLE #tblBlocks

    If I join to the Table above on TemplateID - I need it to return nothing if any BlockID group does not contain a BaseStage of 1.

    Sorry for screwing up.

    All you did is add another column to the original table. You really haven't cleared anything up at all. Show us what the expected results should be. I can't figure out what you are trying to accomplish from what you have posted so far.

  • CREATE TABLE #tblTemplates

    (

    TemplateID int,

    TemplateName varchar(50)

    )

    GO

    INSERT INTO #tblTemplates (TemplateID, TemplateName)

    SELECT 1, 'Template 1' UNION ALL

    SELECT 2, 'Template 2'

    GO

    CREATE TABLE #tblBlocks

    (

    TemplateID int,

    BlockID int,

    FieldID int,

    BaseStage bit

    )

    GO

    INSERT INTO #tblBlocks (TemplateID, BlockID, FieldID, BaseStage)

    SELECT 1, 1, 1, 0 UNION ALL

    SELECT 1, 1, 2, 0 UNION ALL

    SELECT 1, 1, 3, 0 UNION ALL

    SELECT 1, 2, 4, 0 UNION ALL

    SELECT 1, 2, 5, 1 UNION ALL

    SELECT 1, 2, 6, 0 UNION ALL

    SELECT 2, 1, 7, 1 UNION ALL

    SELECT 2, 1, 8, 0 UNION ALL

    SELECT 2, 1, 9, 0 UNION ALL

    SELECT 2, 2, 10, 1 UNION ALL

    SELECT 2, 2, 11, 0 UNION ALL

    SELECT 2, 2, 12, 0

    GO

    SELECT Distinct #tblTemplates.TemplateName FROM #tblTemplates

    INNER JOIN #tblBlocks ON #tblTemplates.TemplateID = #tblBlocks.TemplateID -- plus some more code to satisfy the condition below

    DROP TABLE #tblBlocks

    DROP TABLE #tblTemplates

    A Templates table with two templates in it.

    A Blocks table with BlockIDs in it for each template.

    Each template has 6 BlockID records in #tblBlocks - in two groups of 3. There are 3 BlockID records with a value of 1 and 3 with a value of 2 for each template.

    When I run the statement SELECT * FROM #tblTemplates ... I only want Templates returned that, in the Blocks table, each group of BlockIDs has at least one record with a value of 1 for BaseStage.

    So, if I run the Select statement to return a list of Templates using the data above, I only want to see Template 2 returned. Template 1 does not qualify because it has a group of Block IDs none of which have a value of 1 for BaseStage.

    Thanks again for your help.

  • SELECT BlockID

    FROM #tblBlocks

    GROUP BY BlockID

    HAVING MAX(CASE WHEN BaseStage = 1 THEN 1 ELSE 0 END) = 0

    Include the TemplateID in the SELECT and GROUP BY if required.

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

  • SELECT Distinct #tblTemplates.TemplateName FROM #tblTemplates

    INNER JOIN #tblBlocks ON #tblTemplates.TemplateID = #tblBlocks.TemplateID

    INNER JOIN (SELECT TemplateID, BlockID

    FROM #tblBlocks

    GROUP BY TemplateID, BlockID

    HAVING MAX(CASE WHEN #tblBlocks.BaseStage = 1 THEN 1 ELSE 0 END) = 0) B ON #tblBlocks.TemplateID = B.TemplateID

    The above is returning me Template 1 - but I need the opposite. Template 2 has a BaseStage of 1 set for both groups - so this is okay. Template 1 has no BaseStage of 1 for the first group - so I don't want this returned.

    Thanks for you help.

  • sku370870 (9/7/2012)


    SELECT Distinct #tblTemplates.TemplateName FROM #tblTemplates

    INNER JOIN #tblBlocks ON #tblTemplates.TemplateID = #tblBlocks.TemplateID

    INNER JOIN (SELECT TemplateID, BlockID

    FROM #tblBlocks

    GROUP BY TemplateID, BlockID

    HAVING MAX(CASE WHEN #tblBlocks.BaseStage = 1 THEN 1 ELSE 0 END) = 0) B ON #tblBlocks.TemplateID = B.TemplateID

    The above is returning me Template 1 - but I need the opposite. Template 2 has a BaseStage of 1 set for both groups - so this is okay. Template 1 has no BaseStage of 1 for the first group - so I don't want this returned.

    Thanks for you help.

    Sorry. To check for those including a BackStage of 1, just change the final comparison value:

    HAVING MAX(CASE WHEN #tblBlocks.BaseStage = 1 THEN 1 ELSE 0 END) = 1

    The key thing is that HAVING with CASE can help you in situations like this!

    I based my first answer on the original q :-), viz:

    I need to determine if any group of records in the table above - Grouped by BlockID - does not contain a value of 1 (or true) in the BaseStage column.

    So, to the question - does any group of records, grouped by BlockID, not contain the value 1 (or true) in the records in the above temporary table - the answer would be 'Yes, there is a group without a true BaseStage value.

    How can I ascertain this? I've tried all manner of Group Bys and can't figure it out.

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

  • Since all you want is the TemplateName where BaseStage is 1 in at least 1 record in a group, the following works:

    CREATE TABLE #tblTemplates

    (

    TemplateID int,

    TemplateName varchar(50)

    )

    GO

    INSERT INTO #tblTemplates (TemplateID, TemplateName)

    SELECT 1, 'Template 1' UNION ALL

    SELECT 2, 'Template 2'

    GO

    CREATE TABLE #tblBlocks

    (

    TemplateID int,

    BlockID int,

    FieldID int,

    BaseStage bit

    )

    GO

    INSERT INTO #tblBlocks (TemplateID, BlockID, FieldID, BaseStage)

    SELECT 1, 1, 1, 0 UNION ALL

    SELECT 1, 1, 2, 0 UNION ALL

    SELECT 1, 1, 3, 0 UNION ALL

    SELECT 1, 2, 4, 0 UNION ALL

    SELECT 1, 2, 5, 1 UNION ALL

    SELECT 1, 2, 6, 0 UNION ALL

    SELECT 2, 1, 7, 1 UNION ALL

    SELECT 2, 1, 8, 0 UNION ALL

    SELECT 2, 1, 9, 0 UNION ALL

    SELECT 2, 2, 10, 1 UNION ALL

    SELECT 2, 2, 11, 0 UNION ALL

    SELECT 2, 2, 12, 0

    GO

    SELECT

    *

    FROM

    #tblTemplates t1

    WHERE

    EXISTS (SELECT 1 FROM #tblBlocks tb1 WHERE tb1.TemplateID = t1.TemplateID AND tb1.BaseStage = 1);

    go

    DROP TABLE #tblBlocks

    DROP TABLE #tblTemplates

    GO

  • Hi, I've modified the code:

    SELECT Distinct #tblTemplates.TemplateName

    FROM #tblTemplates

    INNER JOIN #tblBlocks ON #tblTemplates.TemplateID = #tblBlocks.TemplateID

    INNER JOIN (SELECT TemplateID, BlockID

    FROM #tblBlocks

    GROUP BY TemplateID, BlockID

    HAVING MAX(CASE WHEN #tblBlocks.BaseStage = 1 THEN 1 ELSE 0 END) = 1) B ON #tblBlocks.TemplateID = B.TemplateID

    but when I run that, it's returning both Templates - instead of just Template 2.

    Cheers

  • So what you are saying is that BaseStage must be 1 in at least one record of BOTH groups, correct?

Viewing 15 posts - 1 through 15 (of 30 total)

You must be logged in to reply to this topic. Login to reply