One-to-Many entity in a single table

Question

Post reply

One-to-Many entity in a single table

datsun

SSCrazy

Points: 2397

April 16, 2019 at 3:49 pm

#3411633

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

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

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

What exactly do you want the output to look like?

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

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: 2397 More actions · Answer 3

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: 101098 More actions · Answer 4

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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".