locked
Return Rows Where Entire Group Matching RRS feed

  • Question

  • Hi All,

    Running SQL Server 2008 R2.  DDL and Sample Data:

    CREATE TABLE [dbo].[Test]
        (
        [TestID]      [int] NOT NULL,
        [ParentID]    [int] NULL,
        [DocumentID]  [int] NULL,
        [TypeID]      [int] NULL,
        [Value]       [varchar](50) NULL,
        CONSTRAINT [PK_Test_TestID] PRIMARY KEY CLUSTERED ([TestID] ASC)
        WITH
            (
            PAD_INDEX = OFF,
            STATISTICS_NORECOMPUTE = OFF,
            IGNORE_DUP_KEY = OFF,
            ALLOW_ROW_LOCKS = ON,
            ALLOW_PAGE_LOCKS = ON
            )
        ON [PRIMARY]
        )
    ON [PRIMARY]
    GO
    
    INSERT INTO [dbo].[Test]
        ([TestID], [ParentID], [DocumentID], [TypeID], [Value])
    VALUES
        (1, 1, 1, 1, 'M12'),
        (2, 1, 1, 2, '801056'),
        (3, 1, 2, 1, 'M12'),
        (4, 1, 2, 2, '801059'),
        (5, 1, 3, 1, 'M12'),
        (6, 1, 3, 2, '801542'),
        (7, 2, 4, 1, 'M12'),
        (8, 2, 4, 2, '801219')

    I would like to return a list of distinct TypeIDs and Values based on a given ParentID, where each TypeID/Value pair matches.  So for example, the matching query (based on a ParentID of 1) would give me:

    [TypeID]  [Value]
    1         'M12'

    Whereas the non-matching query would return:

    [TypeID] [Value] 2 '801056' 2 '801059' 2 '801542'

    Any assistance is greatly appreciated!

    Best Regards

    Brad


    • Edited by 2012S4 Thursday, July 14, 2016 7:33 PM
    Thursday, July 14, 2016 7:12 PM

Answers

  • Please try this:

    select distinct TypeID,Value from test
    where
    parentID = 1 AND
    typeID in
    (select TypeID
    from test
    group by TypeID
    having count(distinct value) = 1)
    
    
    select distinct TypeID,Value from test
    where
    parentID = 1 AND
    typeID not in
    (select TypeID
    from test
    group by TypeID
    having count(distinct value) = 1)
    
    

    • Marked as answer by 2012S4 Thursday, July 14, 2016 10:13 PM
    Thursday, July 14, 2016 7:56 PM

All replies

  • Please try this:

    select distinct TypeID,Value from test
    where
    parentID = 1 AND
    typeID in
    (select TypeID
    from test
    group by TypeID
    having count(distinct value) = 1)
    
    
    select distinct TypeID,Value from test
    where
    parentID = 1 AND
    typeID not in
    (select TypeID
    from test
    group by TypeID
    having count(distinct value) = 1)
    
    

    • Marked as answer by 2012S4 Thursday, July 14, 2016 10:13 PM
    Thursday, July 14, 2016 7:56 PM
  • DECLARE @Test TABLE
    (
    	[TestID]      [int] NOT NULL,
    	[ParentID]    [int] NULL,
    	[DocumentID]  [int] NULL,
    	[TypeID]      [int] NULL,
    	[Value]       [varchar](50) NULL
    )
    
    
    INSERT INTO @Test
    ([TestID], [ParentID], [DocumentID], [TypeID], [Value])
    VALUES
        (1, 1, 1, 1, 'M12'),
        (2, 1, 1, 2, '801056'),
        (3, 1, 2, 1, 'M12'),
        (4, 1, 2, 2, '801059'),
        (5, 1, 3, 1, 'M12'),
        (6, 1, 3, 2, '801542'),
        (7, 2, 4, 1, 'M12'),
        (8, 2, 4, 2, '801219')
        
    SELECT [TypeID], [Value] 
    FROM @Test 
    WHERE [ParentID] = 1
    GROUP BY [TypeID], [Value]


    A Fan of SSIS, SSRS and SSAS

    Thursday, July 14, 2016 7:57 PM