One-to-Many entity in a single table

  • datsun

    SSCrazy

    Points: 2238

    Hello,

    I need to identify uniquely repeated values from a table which has a field (value one to many IDs).

    Group by clause is not giving me what I need, because as a pair they are distinct.

    CREATE TABLE ##myTable (emailID int, emailaddress VARCHAR(20), activitydate datetime)

    INSERT INTO ##myTable (emailID,emailaddress, activitydate) VALUES (100,'abctest@test.com','18/02/2019')
    INSERT INTO ##myTable (emailID,emailaddress, activitydate) VALUES (102,'xyzabc@test.com','28/01/2019')
    INSERT INTO ##myTable (emailID,emailaddress, activitydate) VALUES (105,'xyzabc@test.com','20/02/2019')
    INSERT INTO ##myTable (emailID,emailaddress, activitydate) VALUES (107,'xyzabc@test.com','22/02/2019')
    INSERT INTO ##myTable (emailID,emailaddress, activitydate) VALUES (117,'twice@test.com','23/03/2019')
    INSERT INTO ##myTable (emailID,emailaddress, activitydate) VALUES (118,'twice@test.com','20/03/2019')
    INSERT INTO ##myTable (emailID,emailaddress, activitydate) VALUES (125,'multiples@test.com','21/03/2019')
    INSERT INTO ##myTable (emailID,emailaddress, activitydate) VALUES (125,'multiples@test.com','22/03/2019')
    INSERT INTO ##myTable (emailID,emailaddress, activitydate) VALUES (125,'multiples@test.com','22/03/2019')
    INSERT INTO ##myTable (emailID,emailaddress, activitydate) VALUES (125,'multiples@test.com','25/03/2019')


    SELECT emailID, emailaddress , count(emailaddress)

    FROM ##myTable

    GROUP BY emailID, emailaddress
    having count(emailaddress) > 1

    DROP TABLE ##myTable

     

    I need to fetch these email addresses from the example table as they occur multiple times with separate IDs.

    'xyzabc@test.com' and 'twice@test.com'

    Thank you in advance.

    Bij

     

  • Lynn Pettis

    SSC Guru

    Points: 442118

    What exactly do you want the output to look like?

     

  • Lynn Pettis

    SSC Guru

    Points: 442118

    One way:

    CREATE TABLE #myTable (emailID int, emailaddress VARCHAR(20), activitydate datetime);
    INSERT INTO #myTable (emailID,emailaddress, activitydate)
    VALUES (100,'abctest@test.com','2019/02/18')
    , (102,'xyzabc@test.com','2019/01/28')
    , (105,'xyzabc@test.com','2019/02/20')
    , (107,'xyzabc@test.com','2019/02/22')
    , (117,'twice@test.com','2019/03/23')
    , (118,'twice@test.com','2019/03/20')
    , (125,'multiples@test.com','2019/03/21')
    , (125,'multiples@test.com','2019/03/22')
    , (125,'multiples@test.com','2019/03/22')
    , (125,'multiples@test.com','2019/03/25');

    WITH UniqueCnts AS (
    SELECT [mt].[emailaddress], [emailIDCnt] = COUNT(DISTINCT [mt].[emailID])
    FROM
    [#myTable] AS [mt]
    GROUP BY
    [mt].[emailaddress]
    )
    SELECT *
    FROM
    [#myTable] AS [mt]
    WHERE
    EXISTS(SELECT 1 FROM [UniqueCnts] AS [uc] WHERE [mt].[emailaddress] = [uc].[emailaddress] AND [uc].[emailIDCnt] > 1);

    DROP TABLE #myTable;
  • datsun

    SSCrazy

    Points: 2238

    Lynn,

    Spot on.  that's exactly what I was after.  I was trying with CTEs too, but you won the race.

    Many thanks for this.

    Bij

  • ScottPletcher

    SSC Guru

    Points: 98053

    SELECT emailaddress, COUNT(DISTINCT emailID) AS emailID_Count
    FROM ##myTable
    GROUP BY emailaddress
    HAVING COUNT(DISTINCT emailID) > 1

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.

Viewing 5 posts - 1 through 5 (of 5 total)

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