SQLTeam.com | Weblogs | Forums

Finding the duplicates

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

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

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.

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

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

please provide sample data

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

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

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'...

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
      )

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

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')

Did you test this DDL and DML?

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

run it on your local server and see what happens

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'

1 Like

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

1 Like

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

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

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