CODEWITHSUNDEEP
sql-server
nikhil
nikhil

Reputation: 5

Select Duplicate column having multiple non distinct parent

Below is my table structure displayed in Image 1

I am creating a query which will give me a result like Image 2

I have created the below query but it is not giving me the expected result

select v.attributeCd , v.parentObject_id  
from Mapping v where v.parentObject_id in ( 
    select distinct(v1.parentObject_id) 
    from Mapping v1)   
group by v.attributeCd , v.parentObject_id 
having count(v.attributeCd) > 2 order by attributeCd

Upvotes: 0

Views: 52

Answers (2)

Akash Patel
Akash Patel

Reputation: 339

As GMB Mentioned you could do a window count.

DECLARE @Mapping AS TABLE
(
    attributeCd         VARCHAR(20)
    ,parentObject_id    INT
)

INSERT INTO @Mapping 
(
    attributeCd 
    ,parentObject_id
)
VALUES
 ('AccountNumber',218)
,('AdditionalWeeks',46)
,('AdminCharges',29)
,('AdminCharges',230)
,('AgeCategoryCd',56)
,('AgeCategoryCd',155)
,('AgentDivisionCd',118)
,('AgentNum',275)
,('AgentNum',445)
,('EntryAge',4)
,('ExpiryDt',181)
,('ExpiryDt',184)
,('ExpiryDt',186)

select  attributeCd ,parentObject_id
FROM
    (select 
        attributeCd
        ,parentObject_id
        ,CNT                = COUNT(*) OVER(PARTITION BY attributeCd)
    from 
        @Mapping) AS V
WHERE
    V.CNT > 1
ORDER BY 
    attributeCd

understand the result of sub query first to get the full picture of query.

I hope this helps.

Upvotes: 1

GMB
GMB

Reputation: 222582

You could do a window count and filter by that:

select attributeCd, parentObject_id
from (select t.*, count(*) over(partition by attributeCd) cnt from mytable)
where cnt > 1

This will give you records for which there are at least another record with the same attributeCd.

Upvotes: 0

Related Questions