Deleting duplicates row for a column using multiple table relation in SQL SERVER

Question

I am containing database for a shop who sells pets like dog, cat etc, for my project.

I have table name Animal , where I have details of animal name , which can be discovery in another table name ANIMAL_DISCOVERY about the type ,whether its a DOG or cat or ETC.

For each dscvr type, I have 4 or 5 category like Expensive, more Expensive, cheap,affordable. This category I am maintaining in a different table name Animal_DISCOVERY_CATEGORY.

and the last table I have is the Animal_DISCOVERY_RULES that a animal should follow as his/her behaviour.

ANIMAL - ANIMAL_NAME,ANIMAL_ID

ANIMAL_DISCOVERY - ANIMAL_DISCOVERY_GROUP, ANIMAL_DISCOVERY_ID

ANIMAL_DISCOVERY_CATEGORY - ANIMAL_DISCOVERY_CATEGORY_ID,ANIMAL_DISCOVERY_CATEGORY_TEXT

ANIMAL_DISCOVERY_RULES - ANIMAL_DISCOVERY_RULES_ID,ANIMAL_DISCOVERY_ID,ANIMAL_DISCOVERY_CATEGORY_ID,ANIMAL_DISCOVERY_RULES_TEXT

Now Here is my issues, I have a lot of data in ANIMAL_DISCOVERY_RULES. For Each Animal type and its category I have duplicates rules in ANIMAL_DISCOVERY_RULES table, which I want to delete preserving only one.

Please help me on writing a query which can perform this action.

Below is the query and result

select appl.ANIMAL_NAME,cate.ANIMAL_DISCOVERY_CATEGORY_TEXT,count(ANIMAL_DISCOVERY_RULES_TEXT) AS RULE_COUNT from ANIMAL_DISCOVERY_RULES rul INNER JOIN
    ANIMAL_DISCOVERY_CATEGORY cate ON rul.ANIMAL_DISCOVERY_CATEGORY_ID=cate .ANIMAL_DISCOVERY_CATEGORY_ID INNER JOIN
    ANIMAL_DISCOVERY grp ON rul.ANIMAL_DISCOVERY_ID=grp.ANIMAL_DISCOVERY_ID INNER JOIN
    ANIMAL appl ON grp.ANIMAL_ID= appl.ANIMAL_ID
    group by cate.ANIMAL_DISCOVERY_CATEGORY_TEXT,appl.ANIMAL_NAME

Animal Name    Category Rules
A1             Dog      Do not use hot water for bath
A1             Cat      Do not use hot water for bath
A1             Dog      Do not use hot water for bath
C1             Dog      Give regular injection
C1             Parrot   Do not give more then 2 chilli a day

What I want is that for animal name A1 , for the category Dog there is repeated rules. I want to delete all these types of duplicate rules for all animal name group by its category.

Hope its more clear.

Are you using mysql or sqlserver? These are not the same thing. Also, you question is very unclear. — Sean Lange
Please show some sample data, expected output, and what you have tried already. Thanks. — Dave Cullum
@t_m.. this is my query to select ... select appl.ANIMAL_NAME,cate.ANIMAL_DISCOVERY_CATEGORY_TEXT,count(ANIMAL_DISCOVERY_RULES_TEXT) AS RULE_COUNT from ANIMAL_DISCOVERY_RULES rul INNER JOIN ANIMAL_DISCOVERY_CATEGORY cate ON rul.ANIMAL_DISCOVERY_CATEGORY_ID=cate .ANIMAL_DISCOVERY_CATEGORY_ID INNER JOIN ANIMAL_DISCOVERY grp ON rul.ANIMAL_DISCOVERY_ID=grp.ANIMAL_DISCOVERY_ID INNER JOIN ANIMAL appl ON grp.ANIMAL_ID= appl.ANIMAL_ID group by cate.ANIMAL_DISCOVERY_CATEGORY_TEXT,appl.ANIMAL_NAME — s_k_t

Sean Sean · Accepted Answer · 2014-08-20T16:07:58

I think you're looking for something like this: (my assumption is your Animal_Discover_Rules_id is the pk and you don't care which rows you keep.)

 ;With DistinctDiscoveryRule AS
 (   
 SELECT appl.ANIMAL_NAME
 ,cate.ANIMAL_DISCOVERY_CATEGORY_TEXT,
 min(ANIMAL_DISCOVERY_RULES_ID) AS ANIMAL_DISCOVERY_RULES_ID 
 from ANIMAL_DISCOVERY_RULES rul INNER JOIN 
 ANIMAL_DISCOVERY_CATEGORY cate ON rul.ANIMAL_DISCOVERY_CATEGORY_ID=cate .ANIMAL_DISCOVERY_CATEGORY_ID INNER JOIN 
 ANIMAL_DISCOVERY grp ON rul.ANIMAL_DISCOVERY_ID=grp.ANIMAL_DISCOVERY_ID INNER JOIN ANIMAL appl ON grp.ANIMAL_ID= appl.ANIMAL_ID 
 group by cate.ANIMAL_DISCOVERY_CATEGORY_TEXT,appl.ANIMAL_NAME

 )
 Delete adr
 FROM   ANIMAL_DISCOVERY_RULES adr LEFT OUTER JOIN
        DistinctDiscoveryRule d on adr.ANIMAL_DISCOVERY_RULES_ID = d.ANIMAL_DISCOVERY_RULES_ID
 WHERE d.ANIMAL_DISCOVERY_RULES_ID IS NULL

Deleting duplicates row for a column using multiple table relation in SQL SERVER

1 Answers