Finding the duplicates

Cenk · October 20, 2020, 5:30pm

Hello,

I am trying to find the duplicates in a table while joining another one. Here are my tables:

**game**
id, productCode, productDescription, status

**gamePins**
id gameID pin serial

SELECT id, productCode, productDescription, status, gb.Pin, gb. Serial
  FROM [GameAPI].[dbo].[GameBanks] g
  inner join GameBankPins gb on g.GameBankID = gb.id
	where gb.pin in (

SELECT gb.pin
FROM [GameAPI].[dbo].[GameBanks] g
inner join GameBankPins gb on g.GameBankID = gb.id
GROUP BY gb.Pin, gb.Serial
HAVING COUNT(*) > 1

)

I wonder if I can only get duplicates which have status = 0 (7,8,9 below)

id	productCode	productDescription	status	id	gameID	pin	serial
1	a	product a	0	1	1	123	445rtg
2	b	product b	1	2	2	123	445rtg
3	c	product c	0	3	3	123	445rtg
4	a	product a	2	4	4	456	abc
5	a	product a	0	5	5	456	abc
6	a	product a	1	6	6	456	abc
7	d	product d	0	7	7	789	11ee
8	a	product a	0	8	8	789	11ee
9	c	product c	0	9	9	789	11ee

imex · October 20, 2020, 5:45pm

Hi,

Try:

with CTE_Count as
(
SELECT id, productCode, productDescription, status, gb.Pin, gb.Serial,
count(1) over(partition by gb.Pin, gb.Serial) as Qty
FROM [GameAPI].[dbo].[GameBanks] g
inner join GameBankPins gb on g.GameBankID = gb.id
where Status = 0
)

select * from CTE_Count
where Qty > 1

Hope this help

Cenk · October 20, 2020, 6:21pm

Unfortunately it is not working as I wanted. Your query gets all of the status = 0 but I want only the group of pins which have status = 0.

imex · October 20, 2020, 6:33pm

Try:

with CTE_Count as
(
    SELECT id, productCode, productDescription, status, gb.Pin, gb.Serial,
        count(1) over(partition by gb.Pin, gb.Serial) as Qty,
        max(status) over(partition by gb.Pin, gb.Serial) as MaxStatus
    FROM [GameAPI].[dbo].[GameBanks] g
    inner join GameBankPins gb on g.GameBankID = gb.id
)

select * from CTE_Count
where Qty > 1 and MaxStatus = 0

ps: Where clause removed from CTE

Hope this help

Cenk · October 20, 2020, 7:15pm

It doesn't bring any records which is fine but I am not sure if the query works

yosiasz · October 20, 2020, 7:16pm

please provide sample data

Cenk · October 20, 2020, 7:29pm

id	productCode	productDescription	status	id	gameID	pin	serial
1	a	product a	0	1	1	123	445rtg
2	b	product b	1	2	2	123	445rtg
3	c	product c	0	3	3	123	445rtg
4	a	product a	2	4	4	456	abc
5	a	product a	0	5	5	456	abc
6	a	product a	1	6	6	456	abc
7	d	product d	0	7	7	789	11ee
8	a	product a	0	8	8	789	11ee
9	c	product c	0	9	9	789	11ee
10	c	product c	0	10	10	456	abc
11	a	product a	1	11	11	erty	0090
12	a	product a	0	12	12	8888	90dpfjd

after grouping only want to get duplicates 7,8,9 which have status=0

yosiasz · October 20, 2020, 7:45pm

please provide this data with ddl and dml

declare @cenk table(id int, productCode )  ----etc

insert into @cenk
select 1, 'a' ---etc

for all of it. help us help you

jeffw8713 · October 20, 2020, 8:10pm

How are you defining duplicates - because none of those rows identified appear to be duplicates. Row 7 is the only row with a product code of 'd' so it cannot be a duplicate row. Row 8 has product code 'a' - pin 789 and serial 11ee and does not duplicate any other rows for product 'a'...

ScottPletcher · October 20, 2020, 8:23pm

SELECT g.id, g.productCode, g.productDescription, g.status, gb.Pin, gb.Serial
  FROM [GameAPI].[dbo].[GameBanks] g
  inner join GameBankPins gb on g.GameBankID = gb.id
  WHERE g.pin IN (
      SELECT gb.pin
      FROM [GameAPI].[dbo].[GameBanks] g
      inner join GameBankPins gb on g.GameBankID = gb.id
      GROUP BY gb.Pin, gb.Serial
      HAVING MAX(g.status) = 0
      )

Cenk · October 21, 2020, 3:11pm

Unfortunately not what I want, I am gonna provide data as @yosiasz suggested.

Cenk · October 22, 2020, 7:08pm

DECLARE @game TABLE (
[GameBankID] [int] IDENTITY(1,1) NOT NULL,
[productCode] nvarchar NULL,
[productDescription] nvarchar NULL,
[status] [int] NOT NULL,
);

DECLARE @gamepins TABLE (
[Id] [int] IDENTITY(1,1) NOT NULL,
[GameBankID] [int] NOT NULL,
[Serial] nvarchar NULL,
[Pin] nvarchar NULL,
);

INSERT INTO @game SELECT(1,'a','product a',0) INSERT INTO @gamepins SELECT(1,1,'123','445rtg')
INSERT INTO @game SELECT(2,'b','product b',1) INSERT INTO @gamepins SELECT(2,2,'123','445rtg')
INSERT INTO @game SELECT(3,'c','product c',0) INSERT INTO @gamepins SELECT(3,3,'123','445rtg')
INSERT INTO @game SELECT(4,'a','product a',2) INSERT INTO @gamepins SELECT(4,4,'456','abc')
INSERT INTO @game SELECT(5,'a','product a',0) INSERT INTO @gamepins SELECT(5,5,'456','abc')
INSERT INTO @game SELECT(6,'a','product a',1) INSERT INTO @gamepins SELECT(6,6,'456','abc')
INSERT INTO @game SELECT(7,'d','product d',0) INSERT INTO @gamepins SELECT(7,7,'789','11ee')
INSERT INTO @game SELECT(8,'a','product a',0) INSERT INTO @gamepins SELECT(8,8,'789','11ee')
INSERT INTO @game SELECT(9,'c','product c',0) INSERT INTO @gamepins SELECT(9,9,'789','11ee')
INSERT INTO @game SELECT(10,'c','product c',0) INSERT INTO @gamepins SELECT(10,10,'456','abc')
INSERT INTO @game SELECT(11,'a','product a',1) INSERT INTO @gamepins SELECT(11,11,'erty','0090')
INSERT INTO @game SELECT(12,'a','product a',0) INSERT INTO @gamepins SELECT(12,12,'8888','90dpfjd')

yosiasz · October 22, 2020, 7:18pm

Did you test this DDL and DML?

Cenk · October 22, 2020, 7:28pm

Nope, but I guess what you are talking about. Removing IDENTITY(1,1) for both tables might work.

yosiasz · October 22, 2020, 7:39pm

run it on your local server and see what happens

Cenk · October 23, 2020, 5:12am

DECLARE @game TABLE (
[GameBankID] [int] NOT NULL,
[productCode] nvarchar NULL,
[productDescription] nvarchar NULL,
[status] [int] NOT NULL
);

DECLARE @gamepins TABLE (
[Id] [int] NOT NULL,
[GameBankID] [int] NOT NULL,
[Serial] nvarchar NULL,
[Pin] nvarchar NULL
);

INSERT INTO @game SELECT 1,'a','product a',0
INSERT INTO @game SELECT 2,'b','product b',1
INSERT INTO @game SELECT 3,'c','product c',0
INSERT INTO @game SELECT 4,'a','product a',2
INSERT INTO @game SELECT 5,'a','product a',0
INSERT INTO @game SELECT 6,'a','product a',1
INSERT INTO @game SELECT 7,'d','product d',0
INSERT INTO @game SELECT 8,'a','product a',0
INSERT INTO @game SELECT 9,'c','product c',0
INSERT INTO @game SELECT 10,'c','product c',0
INSERT INTO @game SELECT 11,'a','product a',1
INSERT INTO @game SELECT 12,'a','product a',0

INSERT INTO @gamepins SELECT 1,1,'123','445rtg'
INSERT INTO @gamepins SELECT 2,2,'123','445rtg'
INSERT INTO @gamepins SELECT 3,3,'123','445rtg'
INSERT INTO @gamepins SELECT 4,4,'456','abc'
INSERT INTO @gamepins SELECT 5,5,'456','abc'
INSERT INTO @gamepins SELECT 6,6,'456','abc'
INSERT INTO @gamepins SELECT 7,7,'789','11ee'
INSERT INTO @gamepins SELECT 8,8,'789','11ee'
INSERT INTO @gamepins SELECT 9,9,'789','11ee'
INSERT INTO @gamepins SELECT 10,10,'456','abc'
INSERT INTO @gamepins SELECT 11,11,'erty','0090'
INSERT INTO @gamepins SELECT 12,12,'8888','90dpfjd'

imex · October 23, 2020, 12:49pm

I believe that the suggestion I posted earlier returns the expected result:

DECLARE @game TABLE (
[GameBankID] [int] NOT NULL,
[productCode] nvarchar(20) NULL,
[productDescription] nvarchar(20) NULL,
[status] [int] NOT NULL
);

DECLARE @gamepins TABLE (
[Id] [int] NOT NULL,
[GameBankID] [int] NOT NULL,
[Pin] nvarchar(20) NULL,
[Serial] nvarchar(20) NULL
);

INSERT INTO @game SELECT 1,'a','product a',0
INSERT INTO @game SELECT 2,'b','product b',1
INSERT INTO @game SELECT 3,'c','product c',0
INSERT INTO @game SELECT 4,'a','product a',2
INSERT INTO @game SELECT 5,'a','product a',0
INSERT INTO @game SELECT 6,'a','product a',1
INSERT INTO @game SELECT 7,'d','product d',0
INSERT INTO @game SELECT 8,'a','product a',0
INSERT INTO @game SELECT 9,'c','product c',0
INSERT INTO @game SELECT 10,'c','product c',0
INSERT INTO @game SELECT 11,'a','product a',1
INSERT INTO @game SELECT 12,'a','product a',0
INSERT INTO @game SELECT 13,'c','product e',0
INSERT INTO @game SELECT 14,'c','product f',1

INSERT INTO @gamepins SELECT 1,1,'123','445rtg'
INSERT INTO @gamepins SELECT 2,2,'123','445rtg'
INSERT INTO @gamepins SELECT 3,3,'123','445rtg'
INSERT INTO @gamepins SELECT 4,4,'456','abc'
INSERT INTO @gamepins SELECT 5,5,'456','abc'
INSERT INTO @gamepins SELECT 6,6,'456','abc'
INSERT INTO @gamepins SELECT 7,7,'789','11ee'
INSERT INTO @gamepins SELECT 8,8,'789','11ee'
INSERT INTO @gamepins SELECT 9,9,'789','11ee'
INSERT INTO @gamepins SELECT 10,10,'456','abc'
INSERT INTO @gamepins SELECT 11,11,'erty','0090'
INSERT INTO @gamepins SELECT 12,12,'8888','90dpfjd'
INSERT INTO @gamepins SELECT 13,9,'789','11eef'
INSERT INTO @gamepins SELECT 14,9,'789','11eef';
 
with CTE_Count as
(
    SELECT id, productCode, productDescription, status, gb.Pin, gb.Serial,
        count(1) over(partition by gb.Pin, gb.Serial) as Qty,
        max(status) over(partition by gb.Pin, gb.Serial) as MaxStatus
    FROM @Game g
    inner join @GamePins gb on g.GameBankID = gb.id
)

select * from CTE_Count
where Qty > 1 and MaxStatus = 0

Hope this help

mike01 · October 23, 2020, 6:34pm

Not sure if your check only applies to Status = 0 or could 3 records all have Status 1 and you want to see them. This approach doesn't look for Status = 0, just that all 3 statuses are the same

IF OBJECT_ID('tempdb..#Game') IS NOT NULL 
    DROP TABLE #Game

IF OBJECT_ID('tempdb..#GamePins') IS NOT NULL 
    DROP TABLE #GamePins

Create table  #Game (
[GameBankID] [int] NOT NULL,
[productCode] nvarchar(100) NULL,
[productDescription] nvarchar(100) NULL,
[status] [int] NOT NULL
);

Create table  #GamePins (
[Id] [int] NOT NULL,
[GameBankID] [int] NOT NULL,
[Serial] nvarchar(100) NULL,
[Pin] nvarchar(100) NULL
);

INSERT INTO #Game SELECT 1,'a','product a',0
INSERT INTO #Game SELECT 2,'b','product b',1
INSERT INTO #Game SELECT 3,'c','product c',0
INSERT INTO #Game SELECT 4,'a','product a',2
INSERT INTO #Game SELECT 5,'a','product a',0
INSERT INTO #Game SELECT 6,'a','product a',1
INSERT INTO #Game SELECT 7,'d','product d',0
INSERT INTO #Game SELECT 8,'a','product a',0
INSERT INTO #Game SELECT 9,'c','product c',0
INSERT INTO #Game SELECT 10,'c','product c',0
INSERT INTO #Game SELECT 11,'a','product a',1
INSERT INTO #Game SELECT 12,'a','product a',0
INSERT INTO #Game SELECT 13,'a','product a',1
INSERT INTO #Game SELECT 14,'a','product a',1

INSERT INTO #gamepins SELECT 1,1,'123','445rtg'
INSERT INTO #gamepins SELECT 2,2,'123','445rtg'
INSERT INTO #gamepins SELECT 3,3,'123','445rtg'
INSERT INTO #gamepins SELECT 4,4,'456','abc'
INSERT INTO #gamepins SELECT 5,5,'456','abc'
INSERT INTO #gamepins SELECT 6,6,'456','abc'
INSERT INTO #gamepins SELECT 7,7,'789','11ee'
INSERT INTO #gamepins SELECT 8,8,'789','11ee'
INSERT INTO #gamepins SELECT 9,9,'789','11ee'
INSERT INTO #gamepins SELECT 10,10,'456','abc'
INSERT INTO #gamepins SELECT 11,11,'erty','0090'
INSERT INTO #gamepins SELECT 12,12,'8888','90dpfjd'
INSERT INTO #gamepins SELECT 13,13,'erty','0090'
INSERT INTO #gamepins SELECT 14,14,'erty','0090'


SELECT gb.Pin, gb.Serial,
    count(1)  as Qty,
    count(Distinct status) as DistinctStatus
FROM #Game g
	inner join #GamePins gb on g.GameBankID = gb.id
group by gb.Pin, gb.Serial
Having count(Distinct status) = 1
  and count(1) > 1

Cenk · October 26, 2020, 3:36pm

Does this query brings only the distinct Pin and Serials?

with CTE_Count as
(
    SELECT id, productCode, productDescription,used, status, gb.Pin, gb.Serial,
        count(1) over(partition by gb.Pin, gb.Serial) as Qty,
        max(status) over(partition by gb.Pin, gb.Serial) as MaxStatus,
		max(used) over(partition by gb.Pin, gb.Serial) as MaxUsed, responseDateTime,referenceId
    FROM GameBanks g
    inner join GameBankPins gb on g.GameBankID = gb.id
)

select * from CTE_Count
where Qty = 1

imex · October 26, 2020, 5:33pm

With the change you made in the Where clause, only rows with a unique combination for columns Pin and Serial should be returned.